package com.egao.orderB.biz.impl;

import com.egao.common.core.damon.constant.UserConstant;
import com.egao.common.core.damon.results.BaseDao;
import com.egao.common.core.damon.results.PageResult2;
import com.egao.common.core.damon.utils.Utils;
import com.egao.common.core.web.JsonResult;
import com.egao.common.system.entity.User;
import com.egao.orderB.biz.BillBiz;
import com.egao.orderB.entity.Bill;
import com.egao.orderB.entity.BillWeekNum;
import com.egao.orderB.entity.WriteOff;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据层实现类
 *
 * @author : Doman
 * @version ：1.0
 * @date : 2020-05-28 14:12:19
 */
@Repository
public class BillBizImpl extends BaseDao implements BillBiz {

  @Override
  public JsonResult list(PageResult2<T> page, Bill bill, User loginUser) throws Exception {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<Bill> rm = new BeanPropertyRowMapper<Bill>(Bill.class);
    StringBuffer sql = new StringBuffer("");
    if (Utils.isNotEmpty(bill.getProvince())) {
      sql.append("select * from (");
    }
    sql.append(" select ");
    if (Utils.isNotEmpty(bill)) {
      if (Utils.isNotEmpty(bill.getFeeTotalS()) && bill.getFeeTotalS().equals("List_some_state")) {
        sql.append(" distinct ");
      }
    }
    sql.append(" b.id,b.organization_id,b.customer_id,b.customer");
    sql.append(",b.contacts,b.contacts_phone,b.dock,b.province");
    sql.append(",b.address,b.weight,b.box_type,b.price");
    sql.append(",b.pack_date,b.vessel,b.voyage,b.bill_of_lading");
    sql.append(",b.barcode_effective_date,b.earliest_arrival_port_date");

//		sql.append(",if(b.factory_address!='',b.factory_address,(select GROUP_CONCAT(af2.faddress) cc from chf_address_factory af2 where af2.id in (b.ef_factory_address) )) factory_address");
//		sql.append(",if(b.factory_phone!='',b.factory_phone,(select GROUP_CONCAT(af2.fphone) cc from chf_address_factory af2 where af2.id in (b.ef_factory_address) )) factory_phone");
//		sql.append(",b.qr_code,b.car_team_id,b.car_team");

    sql.append(",b.factory_address,b.factory_phone,b.qr_code,b.car_team_id,b.car_team");
    sql.append(",b.ef_yard,b.ef_dock");
    sql.append(",b.driver,b.driver_id,b.driver_phone,b.car_no,b.driver_freight,b.box_num");
    sql.append(",b.sys_driver_freight");
    sql.append(",b.sealno,b.sealno_pic,b.box_num_pic,b.arrived_factory_latitude,b.arrived_factory_longitude");
    sql.append(",b.arrived_factory_date,b.arrived_factory_warn_date,b.leaved_factory_date,b.leaved_factory_longitude");
    sql.append(",b.leaved_factory_latitude,b.arrived_harbour_date,b.arrived_harbour_latitude,b.arrived_harbour_longitude");
    sql.append(",b.arrived_harbour_warn_date,b.annex,b.state,b.fee_state_s,b.fee_state_f,b.pre_entry_pic");
    sql.append(",case when b.state='已指派车队' or b.state='已做单' or b.state='已提箱' or b.state='已到厂' or b.state='已离厂' or b.state='已进港'  then '出行中' else '待出行' end carState");
    sql.append(",b.up_yard,b.back_yard,b.remark,b.feeRemark,b.plat_remark,b.finance_remark,b.create_time");
    sql.append(",b.offertime,b.maketime");
    sql.append(",b.salesmanId,b.salesman,b.operaterId,b.operater");
    sql.append(",b.arrival_port,b.sfile,b.white_card,b.sealed_land");
    sql.append(",b.driverReceivetime,b.upBoxtime,b.arrivedFactorytime,b.leaveFactorytime");
    sql.append(",b.inPorttime,b.costBilltime,b.verifytime,b.driverFeeVerifytime,b.customerVerifytime");
    sql.append(",b.create_user,b.update_time,b.update_user");
    sql.append(",cm.email,b.updated,b.drop_box,b.withholding,b.cash");
		/*sql.append(",b.fee_total_s,b.fee_total_f");
		sql.append(",case when cm.invoice_state=1 then FORMAT(b.fee_total_s*0.93,2) else b.fee_total_s END as feeTotalSDiscount ");
		sql.append(",case when cmdr.invoice_state=1 then FORMAT(b.fee_total_f*0.93,2) else b.fee_total_f END as feeTotalFDiscount ");*/
    sql.append(",sognz.organization_name organizationName,suser.nick_name createName ");
    sql.append(",(select GROUP_CONCAT(af.faddress) from chf_address_factory af where af.bill_id=b.id) faddress ");
    sql.append(",boa.apply_id,boa.ocean_id");
    sql.append(" from chf_bill b ");
    sql.append(" inner join sys_user suser on suser.user_id=b.create_user ");
    sql.append(" left join chf_customer cm on cm.id=b.customer_id ");
    sql.append(" left join chf_customer cmdr on cmdr.id=b.car_team_id ");
    sql.append(" left join chf_bill_ocean_apply boa on boa.bill_id=b.id ");
    if (Utils.isNotEmpty(bill)) {
      if (Utils.isNotEmpty(bill.getFeeTotalS()) && bill.getFeeTotalS().equals("List_some_state")) {
        sql.append(" inner join chf_bill_process blp on blp.bill_id=b.id ");
      }
    }
    sql.append(" inner join sys_organization sognz on sognz.organization_id=b.organization_id ");
    if (Utils.isNotEmpty(bill.getFeeTotalF())) {
      sql.append(" inner join (select distinct bps222.bill_id from chf_bill_process bps222 ");
      sql.append(" where bps222.bill_id not in (select distinct bps333.bill_id from chf_bill_process bps333 where bps333.state =:feeTotalF)) blp2 on blp2.bill_id=b.id  ");
      map.put("feeTotalF", bill.getFeeTotalF());
    }
    sql.append(" where b.deleted=0 and cm.deleted=0 and sognz.deleted=0 ");
    if (loginUser.getUserId() != UserConstant.ADMIN_ID) {
      sql.append(" and b.organization_id=:tableOrganizationId ");
      map.put("tableOrganizationId", loginUser.getOrganizationId() + "");
    }
    if (Utils.isNotEmpty(bill)) {
      if (Utils.isNotEmpty(bill.getFeeTotalS()) && bill.getFeeTotalS().equals("List_some_state")) {
        if (Utils.isNotEmpty(bill.getKeywords())) {
          sql.append(" and blp.state=:keywords ");
          map.put("keywords", bill.getKeywords());
        }
      }
			/*if(Utils.isNotEmpty(bill.getFeeTotalF())){
				sql.append(" and not exists (select 1 from chf_bill_process blp2 where blp2.bill_id=b.id and blp2.state =:feeTotalF) ");
				map.put("feeTotalF", bill.getFeeTotalF());
			}*/
      if (Utils.isNotEmpty(bill.getChecked()) && bill.getChecked().equals("未收款")) {
        sql.append(" and (b.fee_state_s is null or b.fee_state_s='') ");
      }
      if (Utils.isNotEmpty(bill.getCustomer())) {
        sql.append(" and instr(b.customer,:customer)>0 ");
        map.put("customer", bill.getCustomer());
      }
      if (Utils.isNotEmpty(bill.getContacts())) {
        sql.append(" and instr(b.contacts,:contacts)>0 ");
        map.put("contacts", bill.getContacts());
      }
      if (Utils.isNotEmpty(bill.getSalesman())) {
        sql.append(" and instr(b.salesman,:salesman)>0 ");
        map.put("salesman", bill.getSalesman());
      }
      if (Utils.isNotEmpty(bill.getOperater())) {
        sql.append(" and instr(b.operater,:operater)>0 ");
        map.put("operater", bill.getOperater());
      }
      if (Utils.isNotEmpty(bill.getBillOfLading())) {
        sql.append(" and instr(b.bill_of_lading,:bill_of_lading)>0 ");
        map.put("bill_of_lading", bill.getBillOfLading());
      }
      if (Utils.isNotEmpty(bill.getCarTeamId())) {
        sql.append(" and instr(b.car_team_id,:car_team_id)>0 ");
        map.put("car_team_id", bill.getCarTeamId());
      }
      if (Utils.isNotEmpty(bill.getCarTeam())) {
        sql.append(" and instr(b.car_team,:car_team)>0 ");
        map.put("car_team", bill.getCarTeam());
      }
      if (Utils.isNotEmpty(bill.getCarNo())) {
        sql.append(" and instr(b.car_no,:car_no)>0 ");
        map.put("car_no", bill.getCarNo());
      }
      if (Utils.isNotEmpty(bill.getDriver())) {
        sql.append(" and instr(b.driver,:driver)>0 ");
        map.put("driver", bill.getDriver());
      }
      if (Utils.isNotEmpty(bill.getDriverPhone())) {
        sql.append(" and instr(b.driver_phone,:driver_phone)>0 ");
        map.put("driver_phone", bill.getDriverPhone());
      }
      if (Utils.isNotEmpty(bill.getBoxNum())) {
        sql.append(" and instr(b.box_num,:box_num)>0 ");
        map.put("box_num", bill.getBoxNum());
      }
      if (Utils.isNotEmpty(bill.getFactoryAddress())) {
        sql.append(" and instr(b.factory_address,:factory_address)>0 ");
        map.put("factory_address", bill.getFactoryAddress());
      }
      if (Utils.isNotEmpty(bill.getState())) {
				/*if(bill.getState().equals("已派单")){
					sql.append(" and b.state in ('已做单','已指派车队') ");
				} else */
        if (bill.getState().equals("进行中")) {
          sql.append(" and b.state in ('已做单','已指派车队','已提箱','已到厂','已离厂','已进港') ");
        } else if (bill.getState().equals("结算")) {
          sql.append(" and b.state in ('发起费用清单','应付费用已审核','应收费用已审核','财务应收应付费用已审核','结单') ");
        } else {
          sql.append(" and instr(b.state,:state)>0 ");
          map.put("state", bill.getState());
        }
      }
      if (Utils.isNotEmpty(bill.getFeeStateF())) {
        sql.append(" and instr(b.fee_state_f,:fee_state_f)>0 ");
        map.put("fee_state_f", bill.getFeeStateF());
      }
      if (Utils.isNotEmpty(bill.getFeeStateS())) {
        sql.append(" and instr(b.fee_state_s,:fee_state_s)>0 ");
        map.put("fee_state_s", bill.getFeeStateS());
      }
      if (Utils.isNotEmpty(bill.getDate1())) {
        sql.append(" and b.pack_date>= :date1 ");
        map.put("date1", bill.getDate1());
      }
      if (Utils.isNotEmpty(bill.getDate2())) {
        sql.append(" and b.pack_date<=:date2 ");
        map.put("date2", bill.getDate2());
      }
      if (Utils.isNotEmpty(bill.getCreateName())) {
        sql.append(" and instr(b.createName,:createName)>0 ");
        map.put("createName", bill.getCreateName());
      }
      if (Utils.isNotEmpty(bill.getWhiteCard())) {
        sql.append(" and instr(b.white_card,:white_card)>0 ");
        map.put("white_card", bill.getWhiteCard());
      }
      if (Utils.isNotEmpty(bill.getArrivalPort())) {
        sql.append(" and instr(b.arrival_port,:arrival_port)>0 ");
        map.put("arrival_port", bill.getArrivalPort());
      }
      if (Utils.isNotEmpty(bill.getUpYard())) {
        sql.append(" and instr(b.up_yard,:up_yard)>0 ");
        map.put("up_yard", bill.getUpYard());
      }
      //withholding == 3  表示查询预提或者落箱的单子
      if (Utils.isNotEmpty(bill.getWithholding()) && bill.getWithholding().equals("3")) {
        sql.append(" and (b.drop_box=1 or b.withholding=1) ");
      } else {
        if (Utils.isNotEmpty(bill.getDropBox())) {
          sql.append(" and b.drop_box=:drop_box ");
          map.put("drop_box", bill.getDropBox());
        }
        if (Utils.isNotEmpty(bill.getWithholding())) {
          sql.append(" and b.withholding=:withholding ");
          map.put("withholding", bill.getWithholding());
        }
      }
      if (Utils.isNotEmpty(bill.getCash())) {
        sql.append(" and b.cash=:cash ");
        map.put("cash", bill.getCash());
      }
      if (Utils.isNotEmpty(bill.getDock())) {
        sql.append(" and b.dock=:dock ");
        map.put("dock", bill.getDock());
      }
      if (Utils.isNotEmpty(bill.getVessel())) {
        sql.append(" and b.vessel=:vessel ");
        map.put("vessel", bill.getVessel());
      }
      if (Utils.isNotEmpty(bill.getVoyage())) {
        sql.append(" and b.voyage=:voyage ");
        map.put("voyage", bill.getVoyage());
      }
      if (Utils.isNotEmpty(bill.getSort()) && Utils.isNotEmpty(bill.getOrder())) {
        sql.append(" order by " + bill.getSort() + " " + bill.getOrder());
      } else if (Utils.isNotEmpty(bill.getSort()) && bill.getSort().equals("customer_id")) {
        sql.append(" order by b.customer_id asc, b.pack_date desc");
      } else {
        sql.append(" order by b.pack_date desc,b.bill_of_lading");
      }
      if (Utils.isNotEmpty(bill.getProvince())) {
        sql.append(") bill2 where 1=1 ");
        sql.append(" and (instr(bill2.province,:province)>0 or instr(bill2.faddress,:province)>0) ");
        map.put("province", bill.getProvince());
      }
    }
    return jsonResult(getPageQueryList(page, sql.toString(), map, rm));
  }

  @Override
  public List<Bill> list(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<Bill> rm = new BeanPropertyRowMapper<Bill>(Bill.class);
    StringBuffer sql = new StringBuffer("select b.id,b.organization_id,b.customer_id,b.customer");
    sql.append(",b.contacts,b.contacts_phone,b.dock,b.province");
    sql.append(",b.address,b.weight,b.box_type,b.price");
    sql.append(",b.pack_date,b.vessel,b.voyage,b.bill_of_lading");
    sql.append(",b.barcode_effective_date,b.earliest_arrival_port_date");
    sql.append(",b.factory_address,b.factory_phone,b.qr_code,b.car_team_id,b.car_team");
    sql.append(",b.ef_yard,b.ef_dock");
    sql.append(",b.driver,b.driver_id,b.driver_phone,b.car_no,b.driver_freight,b.box_num");
    sql.append(",b.sealno,b.sealno_pic,b.box_num_pic,b.arrived_factory_latitude,b.arrived_factory_longitude");
    sql.append(",b.arrived_factory_date,b.arrived_factory_warn_date,b.leaved_factory_date,b.leaved_factory_longitude");
    sql.append(",b.leaved_factory_latitude,b.arrived_harbour_date,b.arrived_harbour_latitude,b.arrived_harbour_longitude");
    sql.append(",b.arrived_harbour_warn_date,b.annex,b.state,b.fee_state_s,b.fee_state_f,b.pre_entry_pic");
    sql.append(",b.up_yard,b.back_yard,b.remark,b.feeRemark,b.plat_remark,b.finance_remark,b.create_time");
    sql.append(",b.offertime,b.maketime");
    sql.append(",b.salesmanId,b.salesman,b.operaterId,b.operater");
    sql.append(",b.arrival_port,b.sfile,b.white_card,b.sealed_land");
    sql.append(",b.driverReceivetime,b.upBoxtime,b.arrivedFactorytime,b.leaveFactorytime");
    sql.append(",b.inPorttime,b.costBilltime,b.verifytime,b.driverFeeVerifytime,b.customerVerifytime");
    sql.append(",b.create_user,b.update_time,b.update_user");
    sql.append(",cm.email,b.updated,b.drop_box,b.withholding,b.cash");
    sql.append(",sognz.organization_name organizationName,suser.nick_name createName ");
    sql.append(" from chf_bill b ");
    sql.append(" inner join sys_user suser on suser.user_id=b.create_user ");
    sql.append(" left join chf_customer cm on cm.id=b.customer_id ");
    sql.append(" inner join sys_organization sognz on sognz.organization_id=b.organization_id ");
    sql.append(" where b.deleted=0 and cm.deleted=0 and sognz.deleted=0 ");
    if (loginUser.getUserId() != UserConstant.ADMIN_ID) {
      sql.append(" and b.organization_id=:tableOrganizationId ");
      map.put("tableOrganizationId", loginUser.getOrganizationId() + "");
    }
    if (Utils.isNotEmpty(bill)) {
      if (Utils.isNotEmpty(bill.getKeywords()) && bill.getKeywords().equals("提醒没传箱封号")) {
        sql.append(" and b.sealno_pic!='' and b.box_num_pic!='' ");
      }
      if (Utils.isNotEmpty(bill.getCustomer())) {
        sql.append(" and instr(b.customer,:customer)>0 ");
        map.put("customer", bill.getCustomer());
      }
      if (Utils.isNotEmpty(bill.getContacts())) {
        sql.append(" and instr(b.contacts,:contacts)>0 ");
        map.put("contacts", bill.getContacts());
      }
      if (Utils.isNotEmpty(bill.getSalesman())) {
        sql.append(" and instr(b.salesman,:salesman)>0 ");
        map.put("salesman", bill.getSalesman());
      }
      if (Utils.isNotEmpty(bill.getOperater())) {
        sql.append(" and instr(b.operater,:operater)>0 ");
        map.put("operater", bill.getOperater());
      }
      if (Utils.isNotEmpty(bill.getBillOfLading())) {
        sql.append(" and instr(b.bill_of_lading,:bill_of_lading)>0 ");
        map.put("bill_of_lading", bill.getBillOfLading());
      }
      if (Utils.isNotEmpty(bill.getCarTeamId())) {
        sql.append(" and instr(b.car_team_id,:car_team_id)>0 ");
        map.put("car_team_id", bill.getCarTeamId());
      }
      if (Utils.isNotEmpty(bill.getCarTeam())) {
        sql.append(" and instr(b.car_team,:car_team)>0 ");
        map.put("car_team", bill.getCarTeam());
      }
      if (Utils.isNotEmpty(bill.getCarNo())) {
        sql.append(" and instr(b.car_no,:car_no)>0 ");
        map.put("car_no", bill.getCarNo());
      }
      if (Utils.isNotEmpty(bill.getDriver())) {
        sql.append(" and instr(b.driver,:driver)>0 ");
        map.put("driver", bill.getDriver());
      }
      if (Utils.isNotEmpty(bill.getDriverPhone())) {
        sql.append(" and instr(b.driver_phone,:driver_phone)>0 ");
        map.put("driver_phone", bill.getDriverPhone());
      }
      if (Utils.isNotEmpty(bill.getBoxNum())) {
        sql.append(" and instr(b.box_num,:box_num)>0 ");
        map.put("box_num", bill.getBoxNum());
      }
      if (Utils.isNotEmpty(bill.getFactoryAddress())) {
        sql.append(" and instr(b.factory_address,:factory_address)>0 ");
        map.put("factory_address", bill.getFactoryAddress());
      }
      if (Utils.isNotEmpty(bill.getState())) {
        sql.append(" and instr(b.state,:state)>0 ");
        map.put("state", bill.getState());
      }
      if (Utils.isNotEmpty(bill.getFeeStateF())) {
        sql.append(" and instr(b.fee_state_f,:fee_state_f)>0 ");
        map.put("fee_state_f", bill.getFeeStateF());
      }
      if (Utils.isNotEmpty(bill.getFeeStateS())) {
        sql.append(" and instr(b.fee_state_s,:fee_state_s)>0 ");
        map.put("fee_state_s", bill.getFeeStateS());
      }
      if (Utils.isNotEmpty(bill.getDate1())) {
        sql.append(" and b.pack_date>= :date1 ");
        map.put("date1", bill.getDate1());
      }
      if (Utils.isNotEmpty(bill.getDate2())) {
        sql.append(" and b.pack_date<=:date2 ");
        map.put("date2", bill.getDate2());
      }
      if (Utils.isNotEmpty(bill.getCreateName())) {
        sql.append(" and instr(b.createName,:createName)>0 ");
        map.put("createName", bill.getCreateName());
      }
      if (Utils.isNotEmpty(bill.getWhiteCard())) {
        sql.append(" and instr(b.white_card,:white_card)>0 ");
        map.put("white_card", bill.getWhiteCard());
      }
      if (Utils.isNotEmpty(bill.getArrivalPort())) {
        sql.append(" and instr(b.arrival_port,:arrival_port)>0 ");
        map.put("arrival_port", bill.getArrivalPort());
      }
      if (Utils.isNotEmpty(bill.getUpYard())) {
        sql.append(" and instr(b.up_yard,:up_yard)>0 ");
        map.put("up_yard", bill.getUpYard());
      }
      if (Utils.isNotEmpty(bill.getProvince())) {
        sql.append(" and instr(b.province,:province)>0 ");
        map.put("province", bill.getProvince());
      }
      //withholding == 3  表示查询预提或者落箱的单子
      if (Utils.isNotEmpty(bill.getWithholding()) && bill.getWithholding().equals("3")) {
        sql.append(" and (b.drop_box=1 or b.withholding=1) ");
      } else {
        if (Utils.isNotEmpty(bill.getDropBox())) {
          sql.append(" and b.drop_box=:drop_box ");
          map.put("drop_box", bill.getDropBox());
        }
        if (Utils.isNotEmpty(bill.getWithholding())) {
          sql.append(" and b.withholding=:withholding ");
          map.put("withholding", bill.getWithholding());
        }
      }
      if (Utils.isNotEmpty(bill.getCash())) {
        sql.append(" and b.cash=:cash ");
        map.put("cash", bill.getCash());
      }
      if (Utils.isNotEmpty(bill.getDock())) {
        sql.append(" and b.dock=:dock ");
        map.put("dock", bill.getDock());
      }
      if (Utils.isNotEmpty(bill.getSort()) && Utils.isNotEmpty(bill.getOrder())) {
        sql.append(" order by " + bill.getSort() + " " + bill.getOrder());
      } else {
        sql.append(" order by b.customer_id asc, b.pack_date desc");
      }
    }
    getMapParameter(sql, map);
    return jdbcTemplate.query(sql.toString(), map, rm);
  }


  @Override
  public JsonResult listDel(PageResult2<T> page, Bill bill, User loginUser) throws Exception {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<Bill> rm = new BeanPropertyRowMapper<Bill>(Bill.class);
    StringBuffer sql = new StringBuffer("select ");
    if (Utils.isNotEmpty(bill)) {
      if (Utils.isNotEmpty(bill.getFeeTotalS()) && bill.getFeeTotalS().equals("List_some_state")) {
        sql.append(" distinct ");
      }
    }
    sql.append(" b.id,b.organization_id,b.customer_id,b.customer");
    sql.append(",b.contacts,b.contacts_phone,b.dock,b.province");
    sql.append(",b.address,b.weight,b.box_type,b.price");
    sql.append(",b.pack_date,b.vessel,b.voyage,b.bill_of_lading");
    sql.append(",b.barcode_effective_date,b.earliest_arrival_port_date");
    sql.append(",b.factory_address,b.factory_phone,b.qr_code,b.car_team_id,b.car_team");
    sql.append(",b.ef_yard,b.ef_dock");
    sql.append(",b.driver,b.driver_id,b.driver_phone,b.car_no,b.driver_freight,b.box_num");
    sql.append(",b.sys_driver_freight");
    sql.append(",b.sealno,b.sealno_pic,b.box_num_pic,b.arrived_factory_latitude,b.arrived_factory_longitude");
    sql.append(",b.arrived_factory_date,b.arrived_factory_warn_date,b.leaved_factory_date,b.leaved_factory_longitude");
    sql.append(",b.leaved_factory_latitude,b.arrived_harbour_date,b.arrived_harbour_latitude,b.arrived_harbour_longitude");
    sql.append(",b.arrived_harbour_warn_date,b.annex,b.state,b.fee_state_s,b.fee_state_f,b.pre_entry_pic");
    sql.append(",case when b.state='已指派车队' or b.state='已做单' or b.state='已提箱' or b.state='已到厂' or b.state='已离厂' or b.state='已进港'  then '出行中' else '待出行' end carState");
    sql.append(",b.up_yard,b.back_yard,b.remark,b.feeRemark,b.plat_remark,b.finance_remark,b.create_time");
    sql.append(",b.offertime,b.maketime");
    sql.append(",b.salesmanId,b.salesman,b.operaterId,b.operater");
    sql.append(",b.arrival_port,b.sfile,b.white_card,b.sealed_land");
    sql.append(",b.driverReceivetime,b.upBoxtime,b.arrivedFactorytime,b.leaveFactorytime");
    sql.append(",b.inPorttime,b.costBilltime,b.verifytime,b.driverFeeVerifytime,b.customerVerifytime");
    sql.append(",b.create_user,b.update_time,b.update_user");
    sql.append(",cm.email,b.updated,b.drop_box,b.withholding,b.cash");
		/*sql.append(",b.fee_total_s,b.fee_total_f");
		sql.append(",case when cm.invoice_state=1 then FORMAT(b.fee_total_s*0.93,2) else b.fee_total_s END as feeTotalSDiscount ");
		sql.append(",case when cmdr.invoice_state=1 then FORMAT(b.fee_total_f*0.93,2) else b.fee_total_f END as feeTotalFDiscount ");*/
    sql.append(",sognz.organization_name organizationName,suser.nick_name createName ");
    sql.append(" from chf_bill b ");
    sql.append(" inner join sys_user suser on suser.user_id=b.create_user ");
    sql.append(" left join chf_customer cm on cm.id=b.customer_id ");
    sql.append(" left join chf_customer cmdr on cmdr.id=b.car_team_id ");
    if (Utils.isNotEmpty(bill)) {
      if (Utils.isNotEmpty(bill.getFeeTotalS()) && bill.getFeeTotalS().equals("List_some_state")) {
        sql.append(" inner join chf_bill_process blp on blp.bill_id=b.id ");
      }
    }
    sql.append(" inner join sys_organization sognz on sognz.organization_id=b.organization_id ");
    if (Utils.isNotEmpty(bill.getFeeTotalF())) {
      sql.append(" inner join (select distinct bps222.bill_id from chf_bill_process bps222 ");
      sql.append(" where bps222.bill_id not in (select distinct bps333.bill_id from chf_bill_process bps333 where bps333.state =:feeTotalF)) blp2 on blp2.bill_id=b.id  ");
      map.put("feeTotalF", bill.getFeeTotalF());
    }
    sql.append(" where b.deleted=1  ");

    if (Utils.isNotEmpty(bill.getCustomer())) {
      sql.append(" and instr(b.customer,:customer)>0 ");
      map.put("customer", bill.getCustomer());
    }
    if (Utils.isNotEmpty(bill.getContacts())) {
      sql.append(" and instr(b.contacts,:contacts)>0 ");
      map.put("contacts", bill.getContacts());
    }
    if (Utils.isNotEmpty(bill.getSalesman())) {
      sql.append(" and instr(b.salesman,:salesman)>0 ");
      map.put("salesman", bill.getSalesman());
    }
    if (Utils.isNotEmpty(bill.getOperater())) {
      sql.append(" and instr(b.operater,:operater)>0 ");
      map.put("operater", bill.getOperater());
    }
    if (Utils.isNotEmpty(bill.getBillOfLading())) {
      sql.append(" and instr(b.bill_of_lading,:bill_of_lading)>0 ");
      map.put("bill_of_lading", bill.getBillOfLading());
    }
    if (Utils.isNotEmpty(bill.getCarTeamId())) {
      sql.append(" and instr(b.car_team_id,:car_team_id)>0 ");
      map.put("car_team_id", bill.getCarTeamId());
    }
    if (Utils.isNotEmpty(bill.getCarTeam())) {
      sql.append(" and instr(b.car_team,:car_team)>0 ");
      map.put("car_team", bill.getCarTeam());
    }
    if (Utils.isNotEmpty(bill.getCarNo())) {
      sql.append(" and instr(b.car_no,:car_no)>0 ");
      map.put("car_no", bill.getCarNo());
    }
    if (Utils.isNotEmpty(bill.getDriver())) {
      sql.append(" and instr(b.driver,:driver)>0 ");
      map.put("driver", bill.getDriver());
    }
    if (Utils.isNotEmpty(bill.getDriverPhone())) {
      sql.append(" and instr(b.driver_phone,:driver_phone)>0 ");
      map.put("driver_phone", bill.getDriverPhone());
    }
    if (Utils.isNotEmpty(bill.getBoxNum())) {
      sql.append(" and instr(b.box_num,:box_num)>0 ");
      map.put("box_num", bill.getBoxNum());
    }
    if (Utils.isNotEmpty(bill.getFactoryAddress())) {
      sql.append(" and instr(b.factory_address,:factory_address)>0 ");
      map.put("factory_address", bill.getFactoryAddress());
    }
    if (Utils.isNotEmpty(bill.getState())) {
      sql.append(" and instr(b.state,:state)>0 ");
      map.put("state", bill.getState());
    }
    if (Utils.isNotEmpty(bill.getFeeStateF())) {
      sql.append(" and instr(b.fee_state_f,:fee_state_f)>0 ");
      map.put("fee_state_f", bill.getFeeStateF());
    }
    if (Utils.isNotEmpty(bill.getFeeStateS())) {
      sql.append(" and instr(b.fee_state_s,:fee_state_s)>0 ");
      map.put("fee_state_s", bill.getFeeStateS());
    }
    if (Utils.isNotEmpty(bill.getDate1())) {
      sql.append(" and b.pack_date>= :date1 ");
      map.put("date1", bill.getDate1());
    }
    if (Utils.isNotEmpty(bill.getDate2())) {
      sql.append(" and b.pack_date<=:date2 ");
      map.put("date2", bill.getDate2());
    }
    if (Utils.isNotEmpty(bill.getCreateName())) {
      sql.append(" and instr(b.createName,:createName)>0 ");
      map.put("createName", bill.getCreateName());
    }
    if (Utils.isNotEmpty(bill.getWhiteCard())) {
      sql.append(" and instr(b.white_card,:white_card)>0 ");
      map.put("white_card", bill.getWhiteCard());
    }
    if (Utils.isNotEmpty(bill.getArrivalPort())) {
      sql.append(" and instr(b.arrival_port,:arrival_port)>0 ");
      map.put("arrival_port", bill.getArrivalPort());
    }
    if (Utils.isNotEmpty(bill.getUpYard())) {
      sql.append(" and instr(b.up_yard,:up_yard)>0 ");
      map.put("up_yard", bill.getUpYard());
    }
    if (Utils.isNotEmpty(bill.getProvince())) {
      sql.append(" and instr(b.province,:province)>0 ");
      map.put("province", bill.getProvince());
    }
    //withholding == 3  表示查询预提或者落箱的单子
    if (Utils.isNotEmpty(bill.getWithholding()) && bill.getWithholding().equals("3")) {
      sql.append(" and (b.drop_box=1 or b.withholding=1) ");
    } else {
      if (Utils.isNotEmpty(bill.getDropBox())) {
        sql.append(" and b.drop_box=:drop_box ");
        map.put("drop_box", bill.getDropBox());
      }
      if (Utils.isNotEmpty(bill.getWithholding())) {
        sql.append(" and b.withholding=:withholding ");
        map.put("withholding", bill.getWithholding());
      }
    }
    if (Utils.isNotEmpty(bill.getCash())) {
      sql.append(" and b.cash=:cash ");
      map.put("cash", bill.getCash());
    }
    if (Utils.isNotEmpty(bill.getDock())) {
      sql.append(" and b.dock=:dock ");
      map.put("dock", bill.getDock());
    }
    sql.append(" order by b.customer_id asc, b.pack_date desc");

    return jsonResult(getPageQueryList(page, sql.toString(), map, rm));
  }

  @Override
  public List<Bill> calendarBillNum(Bill bill, User loginUser) throws Exception {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<Bill> rm = new BeanPropertyRowMapper<Bill>(Bill.class);
    StringBuffer sql = new StringBuffer("select  sum(1) boxNum ");
    sql.append(" ,fbi.pack_date ");
    sql.append(" from ( ");
    sql.append(" select bb.id, DATE_FORMAT(bb.pack_date,'%d') pack_date from chf_bill bb ");
    sql.append(" inner join sys_organization sognz on sognz.organization_id=bb.organization_id and sognz.deleted=0 ");
//		sql.append(" left join chf_bill_process blp on blp.bill_id=bb.id ");
    sql.append(" where bb.deleted=0 ");
    sql.append(" and bb.state in ('发起费用清单','应付费用已审核','应收费用已审核','财务应收应付费用已审核','结单') ");
    if (loginUser.getUserId() != UserConstant.ADMIN_ID) {
      sql.append(" and bb.organization_id=:tableOrganizationId ");
      map.put("tableOrganizationId", loginUser.getOrganizationId() + "");
    }
//		sql.append(" and (blp.state='"+UserConstant.CUSTOMER_FEE_EXAMINE+"' or blp.state='"+UserConstant.DRIVE_FEE_EXAMINE+"') ");
    if (Utils.isNotEmpty(bill)) {
      if (Utils.isNotEmpty(bill.getCustomer())) {
        sql.append(" and bb.customer=:customer ");
        map.put("customer", bill.getCustomer());
      }
      if (Utils.isNotEmpty(bill.getDriverPhone())) {
        sql.append(" and bb.driver_phone=:driver_phone ");
        map.put("driver_phone", bill.getDriverPhone());
      }
      if (Utils.isNotEmpty(bill.getCarTeamId())) {
        sql.append(" and bb.car_team_id=:car_team_id ");
        map.put("car_team_id", bill.getCarTeamId());
      }
      if (Utils.isNotEmpty(bill.getDate1())) {
        sql.append(" and bb.pack_date>= :date1 ");
        map.put("date1", bill.getDate1());
      }
      if (Utils.isNotEmpty(bill.getDate2())) {
        sql.append(" and bb.pack_date<=:date2 ");
        map.put("date2", bill.getDate2());
      }
    }
    sql.append(" ) fbi ");
    sql.append(" GROUP BY fbi.pack_date ");
    getMapParameter(sql, map);
    return jdbcTemplate.query(sql.toString(), map, rm);
  }

  @Override
  public List<Bill> normalCarList(List<String> carNoes) {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<Bill> rm = new BeanPropertyRowMapper<Bill>(Bill.class);
    StringBuffer sql = new StringBuffer("");
    sql.append(" select distinct b.driver,b.driver_id,b.driver_phone,b.customer ");
//        sql.append(" ,GROUP_CONCAT(concat(TRIM(b.car_no),'_2')) as car_no,GROUP_CONCAT(b.id) as bill_of_lading from chf_bill b ");
    sql.append(" ,TRIM(b.car_no) as car_no,b.id,b.bill_of_lading  from chf_bill b ");
    sql.append(" where b.state in ('已做单','已提箱','已到厂','已离厂') ");
    sql.append(" and b.car_no is not null and b.car_no!='' ");
    sql.append(" and date_format(b.pack_date,'%Y-%m-%d') >= date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m-%d') ");
    sql.append(" and b.deleted=0 ");
    sql.append(" and b.car_no in (:carNo) ");
    Map<String, Object> paramMap = new HashMap<String, Object>();
    paramMap.put("carNo", carNoes);
    getMapParameter(sql, map);
    return jdbcTemplate.query(sql.toString(), paramMap, rm);
  }

  @Override
  public List<Bill> findCarName(Bill bill) {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<Bill> rm = new BeanPropertyRowMapper<Bill>(Bill.class);
    StringBuffer sql = new StringBuffer("");
    sql.append(" select b.id,b.driver,b.driver_id,b.driver_phone,b.customer,b.car_team,b.factory_address ");
    sql.append(" ,b.pack_date ");
    sql.append(" ,CASE b.state WHEN '已做单' THEN b.factory_address ");
    sql.append(" WHEN '已提箱' THEN b.factory_address ");
    sql.append(" WHEN '已离厂' THEN b.dock");
    sql.append(" ELSE b.dock END address ");
//        sql.append(" ,GROUP_CONCAT(concat(TRIM(b.car_no),'_2')) as car_no,GROUP_CONCAT(b.id) as bill_of_lading from chf_bill b ");
    sql.append(",b.state,b.ef_yard,b.ef_dock");
    sql.append(" ,TRIM(b.car_no) as car_no,b.bill_of_lading  ");
    sql.append(" ,b.dock,b.up_yard  ");
    sql.append(" ,fa2.longitude upyardLongitude,fa2.latitude upyardLatitude ");
    sql.append(" ,fa3.longitude dockLongitude,fa3.latitude dockLatitude ");
    sql.append(" from chf_bill b ");
    sql.append(" left join chf_maintain_fee_attach fa2 on fa2.id=b.ef_yard  ");
    sql.append(" left join chf_maintain_fee_attach fa3 on fa3.id=b.ef_dock  ");
    sql.append(" where 1=1 ");
    sql.append(" and b.deleted=0 ");
    sql.append(" and b.state in ('已做单','已提箱','已到厂','已离厂') ");
    sql.append(" and b.car_no is not null and b.car_no!='' ");
    sql.append(" and date_format(b.pack_date,'%Y-%m-%d') >= date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m-%d') ");
    sql.append(" and not exists (select 1 from chf_bill_process bp where bp.state='落箱' and bp.bill_id=b.id) ");
    if (Utils.isNotEmpty(bill)) {
      if (Utils.isNotEmpty(bill.getCustomer())) {
        sql.append(" and b.customer=:customer ");
        map.put("customer", bill.getCustomer());
      }
      if (Utils.isNotEmpty(bill.getCarNo())) {
        sql.append(" and b.car_no=:car_no ");
        map.put("car_no", bill.getCarNo());
      }
      if (Utils.isNotEmpty(bill.getId())) {
        sql.append(" and b.id=:id ");
        map.put("id", bill.getId());
      }
    }
    getMapParameter(sql, map);
    return jdbcTemplate.query(sql.toString(), map, rm);
  }

  @Override
  public List<Bill> list(Bill bill) {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<Bill> rm = new BeanPropertyRowMapper<Bill>(Bill.class);
    StringBuffer sql = new StringBuffer("");
    sql.append(" select * from chf_bill b ");
    sql.append(" where b.state in ('已做单','已提箱','已到厂','已离厂','已进港') ");
    sql.append(" and b.car_no is not null and b.car_no!='' ");
    sql.append(" and date_format(b.pack_date,'%Y-%m-%d') >= date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m-%d') ");
    sql.append(" and b.deleted=0 ");

    if (Utils.isNotEmpty(bill)) {
      if (Utils.isNotEmpty(bill.getCustomer())) {
        sql.append(" and bb.customer=:customer ");
        map.put("customer", bill.getCustomer());
      }
    }
    getMapParameter(sql, map);
    return jdbcTemplate.query(sql.toString(), map, rm);
  }

  @Override
  public List<Bill> exportsList(Bill bill) {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<Bill> rm = new BeanPropertyRowMapper<Bill>(Bill.class);
    StringBuffer sql = new StringBuffer("");
    sql.append(" select ");
    sql.append(" b.id,b.organization_id,b.customer_id,b.customer");
    sql.append(",b.contacts,b.contacts_phone,b.dock,b.province");
    sql.append(",b.address,b.weight,b.box_type,b.price");
    sql.append(",b.pack_date,b.vessel,b.voyage,b.bill_of_lading");
    sql.append(",b.barcode_effective_date,b.earliest_arrival_port_date");
    sql.append(",b.factory_address,b.factory_phone,b.qr_code,b.car_team_id,b.car_team");
    sql.append(",b.ef_yard,b.ef_dock");
    sql.append(",b.driver,b.driver_id,b.driver_phone,b.car_no,b.driver_freight,b.box_num");
    sql.append(",b.sys_driver_freight");
    sql.append(",b.sealno,b.sealno_pic,b.box_num_pic");
    sql.append(",b.annex,b.state,b.fee_state_s,b.fee_state_f,b.pre_entry_pic");
    sql.append(",b.up_yard,b.back_yard,b.remark,b.feeRemark,b.plat_remark,b.finance_remark,b.create_time");
    sql.append(",b.offertime,b.maketime");
    sql.append(",b.salesmanId,b.salesman,b.operaterId,b.operater");
    sql.append(",b.arrival_port,b.sfile,b.white_card,b.sealed_land");
    sql.append(",b.updated,b.drop_box,b.withholding,b.cash");
    sql.append(",b.create_user,b.update_time,b.update_user");
    sql.append(" from chf_bill b ");
    sql.append(" where b.deleted=0 ");
//		sql.append(" and instr(',应付费用已审核,应收费用已审核,财务应收应付费用已审核,结单,',b.state)>0 ");
    if (Utils.isNotEmpty(bill)) {
      if (Utils.isNotEmpty(bill.getBillOfLading())) {
        sql.append(" and instr(b.bill_of_lading,:bill_of_lading)>0 ");
        map.put("bill_of_lading", bill.getBillOfLading());
      }
      if (Utils.isNotEmpty(bill.getBoxNum())) {
        sql.append(" and instr(b.box_num,:box_num)>0 ");
        map.put("box_num", bill.getBoxNum());
      }
      if (Utils.isNotEmpty(bill.getDate1())) {
        sql.append(" and b.pack_date>= :date1 ");
        map.put("date1", bill.getDate1());
      }
      if (Utils.isNotEmpty(bill.getDate2())) {
        sql.append(" and b.pack_date<=:date2 ");
        map.put("date2", bill.getDate2());
      }
      if (Utils.isNotEmpty(bill.getCustomer())) {
        sql.append(" and b.customer=:customer ");
        map.put("customer", bill.getCustomer());
      }
      if (Utils.isNotEmpty(bill.getCarTeam())) {
        sql.append(" and instr(b.car_team,:car_team)>0 ");
        map.put("car_team", bill.getCarTeam());
      }
      if (Utils.isNotEmpty(bill.getCarNo())) {
        sql.append(" and instr(b.car_no,:car_no)>0 ");
        map.put("car_no", bill.getCarNo());
      }
      if (Utils.isNotEmpty(bill.getDriver())) {
        sql.append(" and instr(b.driver,:driver)>0 ");
        map.put("driver", bill.getDriver());
      }
      if (Utils.isNotEmpty(bill.getDock())) {
        sql.append(" and instr(b.dock,:dock)>0 ");
        map.put("dock", bill.getDock());
      }
    }
    sql.append(" order by b.pack_date asc");
    getMapParameter(sql, map);
    return jdbcTemplate.query(sql.toString(), map, rm);
  }

  @Override
  public Integer customerOfferTodayNum(Bill bill) {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<Integer> rm = new SingleColumnRowMapper<Integer>(Integer.class);
    StringBuffer sql = new StringBuffer("select count(1) ");
    sql.append(" from chf_bill ");
    sql.append(" where deleted=0 and customer_id=:customer_id ");
    sql.append(" and DATE_FORMAT(pack_date,'%Y-%m-%d') =DATE_FORMAT(:pack_date,'%Y-%m-%d') ");
    map.put("customer_id", bill.getCustomerId());
    map.put("pack_date", bill.getPackDate());
    getMapParameter(sql, map);
    return jdbcTemplate.queryForObject(sql.toString(), map, rm);
  }

  @Override
  public Integer listNum(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<Integer> rm = new SingleColumnRowMapper<Integer>(Integer.class);
    StringBuffer sql = new StringBuffer("select count(1) ");
    sql.append(" from chf_bill b ");
    sql.append(" left join chf_customer cm on cm.id=b.customer_id ");
    sql.append(" inner join sys_organization sognz on sognz.organization_id=b.organization_id ");
    sql.append(" where b.deleted=0 and cm.deleted=0 and sognz.deleted=0 ");
    if (loginUser.getUserId() != UserConstant.ADMIN_ID) {
      sql.append(" and b.organization_id=:tableOrganizationId ");
      map.put("tableOrganizationId", loginUser.getOrganizationId() + "");
    }
    if (Utils.isNotEmpty(bill)) {
      if (Utils.isNotEmpty(bill.getCustomer())) {
        sql.append(" and instr(b.customer,:customer)>0 ");
        map.put("customer", bill.getCustomer());
      }
      if (Utils.isNotEmpty(bill.getDate1())) {
        sql.append(" and b.pack_date>= :date1 ");
        map.put("date1", bill.getDate1());
      }
      if (Utils.isNotEmpty(bill.getDate2())) {
        sql.append(" and b.pack_date<=:date2 ");
        map.put("date2", bill.getDate2());
      }
    }
    getMapParameter(sql, map);
    return jdbcTemplate.queryForObject(sql.toString(), map, rm);
  }

  @Override
  public List<BillWeekNum> listDayNum(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<BillWeekNum> rm = new BeanPropertyRowMapper<BillWeekNum>(BillWeekNum.class);
    StringBuffer sql = new StringBuffer("select ");
    sql.append(" dayT.date ");
    sql.append(" ,count(b.pack_date) num ");
    sql.append(" from (SELECT * FROM (");
    sql.append("SELECT adddate('1970-01-01',t4.i * 10000 + t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) date ");
    sql.append("FROM(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,");
    sql.append("(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,");
    sql.append("(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,");
    sql.append("(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,");
    sql.append("(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) tab ");
    sql.append(" WHERE date BETWEEN DATE_FORMAT(:date3,'%Y-%m-%d') AND DATE_FORMAT(:date4,'%Y-%m-%d')) dayT ");
    map.put("date3", bill.getDate1().substring(0, 10));
    map.put("date4", bill.getDate2().substring(0, 10));
    sql.append(" left join (select b1.pack_date from chf_bill b1 ");
    if (Utils.isNotEmpty(bill.getCustomer())) {
      sql.append(" inner join chf_customer cm on cm.id=b1.customer_id ");
    }
    sql.append(" where b1.deleted=0  ");
    if (Utils.isNotEmpty(bill)) {
      if (Utils.isNotEmpty(bill.getCustomer())) {
        sql.append(" and cm.deleted=0 and instr(b1.customer,:customer)>0 ");
        map.put("customer", bill.getCustomer());
      }
      if (Utils.isNotEmpty(bill.getDate1())) {
        sql.append(" and b1.pack_date>= :date1 ");
        map.put("date1", bill.getDate1());
      }
      if (Utils.isNotEmpty(bill.getDate2())) {
        sql.append(" and b1.pack_date<=:date2 ");
        map.put("date2", bill.getDate2());
      }
    }
    sql.append("  ) b ");
    sql.append(" on DATE_FORMAT(b.pack_date,'%Y-%m-%d')=dayT.date");

    sql.append(" GROUP BY dayT.date ");
    sql.append(" ORDER BY dayT.date asc ");
    getMapParameter(sql, map);
    return jdbcTemplate.query(sql.toString(), map, rm);
  }

  @Override
  public List<BillWeekNum> listWeekNum(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<BillWeekNum> rm = new BeanPropertyRowMapper<BillWeekNum>(BillWeekNum.class);

    StringBuffer sql = new StringBuffer("select  ");
//		sql.append(" (@i:=@i+1)  i, ");
    if (Utils.isNotEmpty(bill)) {
      if (Utils.isNotEmpty(bill.getCarNo())) {
        sql.append(" dr.name carNo, ");
      }
      if (Utils.isNotEmpty(bill.getCustomer())) {
        sql.append(" b.customer, ");
      }
    }
    sql.append(" DATE_FORMAT(b.pack_date,'%u') date, ");
    sql.append(" count(1) num ");
    sql.append(" from chf_bill b ");
    if (Utils.isNotEmpty(bill)) {
      if (Utils.isNotEmpty(bill.getCarNo())) {
        sql.append(" left join chf_driver dr on dr.car_no=b.car_no ");
      }
      if (Utils.isNotEmpty(bill.getCustomer())) {
        sql.append(" left join chf_customer cm on cm.id=b.customer_id ");
      }
    }
//		sql.append(" ,(select   @i:=0)  t2 ");
    sql.append(" where b.deleted=0  ");
    if (Utils.isNotEmpty(bill)) {
      if (Utils.isNotEmpty(bill.getCarNo())) {
        sql.append(" and dr.deleted=0  ");
      }
      if (Utils.isNotEmpty(bill.getCustomer())) {
        sql.append(" and cm.deleted=0 ");
      }
    }
    if (Utils.isNotEmpty(bill)) {
//			if(Utils.isNotEmpty(bill.getCarNo())){
//				sql.append(" and instr(b.carNo,:carNo)>0 ");
//				map.put("carNo", bill.getCarNo());
//			}
      if (Utils.isNotEmpty(bill.getContacts())) {
        sql.append(" and instr(b.customer,:customer)>0 ");
        map.put("customer", bill.getContacts());
      }
      if (Utils.isNotEmpty(bill.getDate1())) {
        sql.append(" and b.pack_date>= :date1 ");
        map.put("date1", bill.getDate1());
      }
      if (Utils.isNotEmpty(bill.getDate2())) {
        sql.append(" and b.pack_date<=:date2 ");
        map.put("date2", bill.getDate2());
      }
    }
    boolean ret = true;
    if (Utils.isNotEmpty(bill)) {
      if (Utils.isNotEmpty(bill.getCarNo())) {
        sql.append(" GROUP BY date,dr.name ");
//				sql.append(" having i<11  ");
        sql.append(" ORDER BY date asc,num desc ");
        ret = false;
      }
      if (Utils.isNotEmpty(bill.getCustomer())) {
        sql.append(" GROUP BY date,customer ");
//				sql.append(" having i<11  ");
        sql.append(" ORDER BY date asc,num desc ");
        ret = false;
      }
    }
    if (ret) {
      sql.append(" GROUP BY date ");
//			sql.append(" having i<11  ");
      sql.append(" ORDER BY date asc ");
    }

    getMapParameter(sql, map);
    List<BillWeekNum> list = jdbcTemplate.query(sql.toString(), map, rm);
//		if(!ret) {
//			int count = 0,total = 10;
//			String date = "";
//			List<BillWeekNum> list2 = new ArrayList<>();
//			if (Utils.isNotEmpty(list)) {
//				for (BillWeekNum billWeekNum : list) {
//					if(date.equals("")) {
//						date = billWeekNum.getDate();
//					}
//					if(date.equals(billWeekNum.getDate())) {
//						if(count<total) {
//							list2.add(billWeekNum);
//						}
//						count ++;
//					} else {
//						count = 0;
//					}
//					date = billWeekNum.getDate();
//				}
//			}
//			return list2;
//		}
    return list;
  }

  @Override
  public Integer getWeek(Bill bill) {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<Integer> rm = new SingleColumnRowMapper<Integer>(Integer.class);

    StringBuffer sql = new StringBuffer("select  ");
    sql.append(" DATE_FORMAT(:date1,'%u') date ");
    sql.append(" from dual ");
    map.put("date1", bill.getDate1());
    getMapParameter(sql, map);
    return jdbcTemplate.queryForObject(sql.toString(), map, rm);
  }

  @Override
  public List<BillWeekNum> listMonthNum(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<BillWeekNum> rm = new BeanPropertyRowMapper<BillWeekNum>(BillWeekNum.class);
    StringBuffer sql = new StringBuffer("select da.date, ");
    if (Utils.isNotEmpty(bill) && Utils.isNotEmpty(bill.getCustomer())) {
      sql.append(" en.customer, ");
    }
    sql.append(" en.num from ");
    sql.append(" (SELECT DATE_FORMAT( DATE_SUB( :date1, INTERVAL 1 MONTH ),'%Y-%m') AS date UNION ALL ");
    sql.append(" SELECT DATE_FORMAT(DATE_SUB( :date1, INTERVAL 0 MONTH ),'%Y-%m') AS date ) da ");
    sql.append(" left join  ");
    sql.append(" (select ");
    if (Utils.isNotEmpty(bill) && Utils.isNotEmpty(bill.getCustomer())) {
      sql.append(" b.customer,");
    }
    sql.append(" DATE_FORMAT(b.pack_date,'%Y-%m') date,count(1) num ");
    sql.append(" from chf_bill b ");
    if (Utils.isNotEmpty(bill) && Utils.isNotEmpty(bill.getCustomer())) {
      sql.append(" left join chf_customer cm on cm.id=b.customer_id ");
    }
    sql.append(" where b.deleted=0 ");
    if (Utils.isNotEmpty(bill) && Utils.isNotEmpty(bill.getCustomer())) {
      sql.append(" and cm.deleted=0 ");
      if (!bill.getCustomer().equals("1")) {
        sql.append(" and instr(b.customer,:customer)>0 ");
        map.put("customer", bill.getCustomer());
      }
    }
    sql.append(" and (DATE_FORMAT(DATE_SUB( b.pack_date, INTERVAL -1 MONTH ),'%Y-%m')=DATE_FORMAT(:date1,'%Y-%m')   ");
    sql.append(" or DATE_FORMAT(DATE_SUB( b.pack_date, INTERVAL 0 MONTH ),'%Y-%m')=DATE_FORMAT(:date1,'%Y-%m')   ");
    sql.append(" ) ");
    sql.append(" GROUP BY date ");
    if (Utils.isNotEmpty(bill) && Utils.isNotEmpty(bill.getCustomer())) {
      sql.append(" ,b.customer ");
    }
    sql.append(" ORDER BY date asc,num desc ");
    sql.append(" ) en ");
    sql.append(" on en.date=da.date ");
    if (Utils.isNotEmpty(bill) && Utils.isNotEmpty(bill.getDate1())) {
      map.put("date1", bill.getDate1());
    }
    getMapParameter(sql, map);
    return jdbcTemplate.query(sql.toString(), map, rm);
  }

  @Override
  public List<BillWeekNum> listMonthCustomerAndDriverNum(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<BillWeekNum> rm = new BeanPropertyRowMapper<BillWeekNum>(BillWeekNum.class);
    StringBuffer sql = new StringBuffer("select ");
//		sql.append(" (@i:=@i+1)  i, ");
    if (Utils.isNotEmpty(bill)) {
      if (Utils.isNotEmpty(bill.getCarNo())) {
        sql.append(" b.driver carNo,   ");
      }
      if (Utils.isNotEmpty(bill.getCustomer())) {
        sql.append(" b.customer,   ");
      }
    }
    sql.append(" DATE_FORMAT(b.pack_date,'%Y-%m') date,count(1) num ");
    sql.append(" from chf_bill b ");
    if (Utils.isNotEmpty(bill)) {
      if (Utils.isNotEmpty(bill.getCustomer())) {
        sql.append(" left join chf_customer cm on cm.id=b.customer_id ");
      }
    }
//		sql.append(" ,(select   @i:=0)  t2 ");
    sql.append(" where b.deleted=0 ");
    if (Utils.isNotEmpty(bill)) {
      if (Utils.isNotEmpty(bill.getCarNo())) {
        sql.append(" and  b.driver!='' and b.driver is not null ");
      }
      if (Utils.isNotEmpty(bill.getCustomer())) {
        sql.append(" and cm.deleted=0 ");
      }
    }
    if (Utils.isNotEmpty(bill) && Utils.isNotEmpty(bill.getCustomer())) {
      sql.append(" and cm.deleted=0 ");
    }
    sql.append(" and DATE_FORMAT(DATE_SUB( b.pack_date, INTERVAL 0 MONTH ),'%Y-%m')=DATE_FORMAT(:date1,'%Y-%m')   ");
    sql.append(" GROUP BY date ");
    if (Utils.isNotEmpty(bill)) {
      if (Utils.isNotEmpty(bill.getCarNo())) {
        sql.append(" ,b.driver ");
      }
      if (Utils.isNotEmpty(bill.getCustomer())) {
        sql.append(" ,b.customer ");
      }
    }
//		sql.append(" having i<6 ");
    sql.append(" ORDER BY date asc,num desc ");
    map.put("date1", bill.getDate1());
    getMapParameter(sql, map);


    List<BillWeekNum> list = jdbcTemplate.query(sql.toString(), map, rm);
    int count = 0, total = 8;
    String date = "";
    List<BillWeekNum> list2 = new ArrayList<>();
    if (Utils.isNotEmpty(list)) {
      for (BillWeekNum billWeekNum : list) {
        if (date.equals("")) {
          date = billWeekNum.getDate();
        }
        if (date.equals(billWeekNum.getDate())) {
          if (count < total) {
            list2.add(billWeekNum);
          }
          count++;
        } else {
          count = 0;
        }
        date = billWeekNum.getDate();
      }
    }
    return list2;
  }

  @Override
  public List<BillWeekNum> listUnPayNum(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<BillWeekNum> rm = new BeanPropertyRowMapper<BillWeekNum>(BillWeekNum.class);
    StringBuffer sql = new StringBuffer("select ");
    sql.append(" count(1) num ");
    sql.append(" from chf_bill b ");
    sql.append(" where b.deleted=0 ");
    if (Utils.isNotEmpty(bill)) {
      if (Utils.isNotEmpty(bill.getFeeStateS()) && bill.getFeeStateS().equals("未收款")) {
        sql.append(" and ( b.fee_state_s='' or b.fee_state_s is null)");
      }
      if (Utils.isNotEmpty(bill.getCustomer())) {
        sql.append(" and instr(b.customer, :customer)>0");
        map.put("customer", bill.getCustomer());
      }
    }
    getMapParameter(sql, map);
    return jdbcTemplate.query(sql.toString(), map, rm);
  }

  @Override
  public List<BillWeekNum> listDockNum(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<BillWeekNum> rm = new BeanPropertyRowMapper<BillWeekNum>(BillWeekNum.class);
    StringBuffer sql = new StringBuffer(" ");
    if (bill.getDock().equals("week")) {
      sql.append(" select a.* from (SELECT af3.faddress carNo, b.dock customer, count(1) num FROM chf_bill b ");
      sql.append(" inner join (select af1.bill_id,af1.faddress from chf_address_factory af1 where af1.longitude !='' and af1.latitude !='' and af1.deleted=0 and not exists (select 1 from chf_address_factory af2 where af1.bill_id = af2.bill_id and af1.create_time > af2.create_time)) af3 on b.id=af3.bill_id ");
      sql.append(" WHERE b.deleted=0 and YEARWEEK(DATE_FORMAT(b.pack_date,'%Y-%m-%d')) = YEARWEEK(NOW()) GROUP BY af3.faddress, b.dock     )  a order by a.num desc ");
    } else if (bill.getDock().equals("month")) {
      sql.append(" select bb.longitude,bb.latitude,a.* from (SELECT af3.faddress carNo, b.dock customer, count(1) num FROM chf_bill b ");
      sql.append(" inner join (select af1.bill_id,af1.faddress from chf_address_factory af1 where af1.longitude !='' and af1.latitude !='' and af1.deleted=0 and not exists (select 1 from chf_address_factory af2 where af1.bill_id = af2.bill_id and af1.create_time > af2.create_time)) af3 on b.id=af3.bill_id ");
      sql.append(" WHERE b.deleted=0 and DATE_FORMAT(b.pack_date,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') GROUP BY af3.faddress, b.dock    )  a ");
      sql.append(" inner join (select af3.id,af3.longitude,af3.latitude,af3.faddress from chf_address_factory af3 where af3.longitude !='' and af3.latitude !='' and af3.deleted=0 and not exists (select 1 from chf_address_factory af4 where af3.faddress = af4.faddress and af3.create_time > af4.create_time and af4.deleted=0 )) bb on a.carNo=bb.faddress ");
      sql.append(" order by a.num desc");
      sql.append(" limit 5");
    } else if (bill.getDock().equals("last month")) {
      sql.append(" select bb.longitude,bb.latitude,a.* from (SELECT af3.faddress carNo, b.dock customer, count(1) num FROM chf_bill b ");
      sql.append(" inner join (select af1.bill_id,af1.faddress from chf_  address_factory af1 where af1.longitude !='' and af1.latitude !='' and af1.deleted=0 and not exists (select 1 from chf_address_factory af2 where af1.bill_id = af2.bill_id and af1.create_time > af2.create_time)) af3 on b.id=af3.bill_id ");
      sql.append(" WHERE b.deleted=0 and period_diff(date_format(now(),'%Y%m'),date_format(b.pack_date,'%Y%m')) =1 GROUP BY af3.faddress, b.dock    )  a  ");
      sql.append(" inner join (select af3.id,af3.longitude,af3.latitude,af3.faddress from chf_address_factory af3 where af3.longitude !='' and af3.latitude !='' and af3.deleted=0 and not exists (select 1 from chf_address_factory af4 where af3.faddress = af4.faddress and af3.create_time > af4.create_time and af4.deleted=0 )) bb on a.carNo=bb.faddress ");
      sql.append(" order by a.num desc");
      sql.append(" limit 5");
    } else if (bill.getDock().equals("quarter")) {
      sql.append(" select bb.longitude,bb.latitude,a.* from (SELECT af3.faddress carNo, b.dock customer, count(1) num FROM chf_bill b ");
      sql.append(" inner join (select af1.bill_id,af1.faddress from chf_address_factory af1 where af1.longitude !='' and af1.latitude !='' and af1.deleted=0 and not exists (select 1 from chf_address_factory af2 where af1.bill_id = af2.bill_id and af1.create_time > af2.create_time)) af3 on b.id=af3.bill_id ");
      sql.append(" WHERE b.deleted=0  and QUARTER(b.pack_date)=QUARTER(NOW()) and DATE_FORMAT(b.pack_date,'%Y') = DATE_FORMAT(NOW(),'%Y') GROUP BY af3.faddress, b.dock )  a  ");
      sql.append(" inner join (select af3.id,af3.longitude,af3.latitude,af3.faddress from chf_address_factory af3 where af3.longitude !='' and af3.latitude !='' and af3.deleted=0 and not exists (select 1 from chf_address_factory af4 where af3.faddress = af4.faddress and af3.create_time > af4.create_time and af4.deleted=0 )) bb on a.carNo=bb.faddress ");
      sql.append(" order by a.num desc");
      sql.append(" limit 5");
    } else if (bill.getDock().equals("last quarter")) {
      sql.append(" select bb.longitude,bb.latitude,a.* from (SELECT af3.faddress carNo, b.dock customer, count(1) num FROM chf_bill b ");
      sql.append(" inner join (select af1.bill_id,af1.faddress from chf_address_factory af1 where af1.longitude !='' and af1.latitude !='' and af1.deleted=0 and not exists (select 1 from chf_address_factory af2 where af1.bill_id = af2.bill_id and af1.create_time > af2.create_time)) af3 on b.id=af3.bill_id ");
      sql.append(" WHERE b.deleted=0  and QUARTER(b.pack_date)=QUARTER(DATE_SUB(now(),INTERVAL 1 QUARTER)) AND year(b.pack_date)=year(DATE_SUB(now(),INTERVAL 1 QUARTER)) GROUP BY af3.faddress, b.dock)  a  ");
      sql.append(" inner join (select af3.id,af3.longitude,af3.latitude,af3.faddress from chf_address_factory af3 where af3.longitude !='' and af3.latitude !='' and af3.deleted=0 and not exists (select 1 from chf_address_factory af4 where af3.faddress = af4.faddress and af3.create_time > af4.create_time and af4.deleted=0 )) bb on a.carNo=bb.faddress ");
      sql.append(" order by a.num desc");
      sql.append(" limit 5");
    } else if (bill.getDock().equals("customer")) {
      sql.append(" select bb.carNo,bb.customer,CASE LEFT(bb.box_type,2)<21 WHEN 1 THEN '小柜' ELSE '大柜' END box_type, CASE ad.price WHEN not null THEN ad.price ELSE mf.price END price  from ");
      sql.append(" (SELECT af3.faddress carNo, b.dock customer, b.box_type, count(1) num FROM chf_bill b ");
      sql.append(" inner join (select af1.bill_id,af1.faddress from chf_address_factory af1 where af1.longitude !='' and af1.latitude !='' and af1.deleted=0 and not exists (select 1 from chf_address_factory af2 where af1.bill_id = af2.bill_id and af1.create_time > af2.create_time)) af3 on b.id=af3.bill_id ");
      sql.append(" WHERE b.deleted=0 and DATE_FORMAT(b.pack_date,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ");
      if (Utils.isNotEmpty(bill.getCustomer())) {
        sql.append(" and instr(b.customer,:customer)>0 ");
        map.put("customer", bill.getCustomer());
      }
      sql.append(" GROUP BY af3.faddress, b.dock,b.box_type  order by num desc  limit 10) bb ");
      sql.append(" left join chf_address ad on ad.address=bb.carNo and ad.deleted=0  and bb.customer=ad.dock and ad.box_type=CASE LEFT(bb.box_type,2)<21 WHEN 1 THEN '小柜' ELSE '大柜' END ");
      sql.append(" left join chf_maintain_fee mf on mf.address=bb.carNo and mf.deleted=0 and bb.customer=mf.dock and mf.box_type=CASE LEFT(bb.box_type,2)<21 WHEN 1 THEN '小柜' ELSE '大柜' END");
    }

//		if(Utils.isNotEmpty(bill.getKeywords()) && bill.getKeywords().equalsIgnoreCase("quarter")) {
//			sql.append(" and QUARTER(b.pack_date)=QUARTER(NOW()) and DATE_FORMAT(b.pack_date,'%Y') = DATE_FORMAT(NOW(),'%Y')");
//		}
//		if(Utils.isNotEmpty(bill.getKeywords()) && bill.getKeywords().equalsIgnoreCase("month")) {
//			sql.append(" and DATE_FORMAT(b.pack_date,'%Y%m') = DATE_FORMAT(NOW(),'%Y%m')");
//		}
//		sql.append(" having count(date)<11  ");
    getMapParameter(sql, map);
    return jdbcTemplate.query(sql.toString(), map, rm);
  }

  @Override
  public List<Bill> listUnExamine() {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<Bill> rm = new BeanPropertyRowMapper<Bill>(Bill.class);
    StringBuffer sql = new StringBuffer("select distinct  ");
    sql.append(" b.id,b.organization_id,b.customer_id,b.customer");
    sql.append(",b.contacts,b.contacts_phone,b.dock,b.province");
    sql.append(",b.address,b.weight,b.box_type,b.price");
    sql.append(",b.pack_date,b.vessel,b.voyage,b.bill_of_lading");
    sql.append(",b.barcode_effective_date,b.earliest_arrival_port_date");
    sql.append(",b.factory_address,b.factory_phone,b.qr_code,b.car_team_id,b.car_team");
    sql.append(",b.ef_yard,b.ef_dock");
    sql.append(",b.driver,b.driver_id,b.driver_phone,b.car_no,b.driver_freight,b.box_num");
    sql.append(",b.sealno,b.sealno_pic,b.box_num_pic,b.arrived_factory_latitude,b.arrived_factory_longitude");
    sql.append(",b.arrived_factory_date,b.arrived_factory_warn_date,b.leaved_factory_date,b.leaved_factory_longitude");
    sql.append(",b.leaved_factory_latitude,b.arrived_harbour_date,b.arrived_harbour_latitude,b.arrived_harbour_longitude");
    sql.append(",b.arrived_harbour_warn_date,b.annex,b.state,b.fee_state_s,b.fee_state_f,b.pre_entry_pic");
    sql.append(",b.up_yard,b.back_yard,b.remark,b.feeRemark,b.plat_remark,b.finance_remark,b.create_time");
    sql.append(",b.offertime,b.maketime");
    sql.append(",b.salesmanId,b.salesman,b.operaterId,b.operater");
    sql.append(",b.arrival_port,b.sfile,b.white_card,b.sealed_land");
    sql.append(",b.driverReceivetime,b.upBoxtime,b.arrivedFactorytime,b.leaveFactorytime");
    sql.append(",b.inPorttime,b.costBilltime,b.verifytime,b.driverFeeVerifytime,b.customerVerifytime");
    sql.append(",b.create_user,b.update_time,b.update_user");
    sql.append(",suser.nick_name createName ");
    sql.append(" from chf_bill b ");
    sql.append(" inner join sys_user suser on suser.user_id=b.create_user ");
    sql.append(" inner join chf_bill_process p on p.bill_id=b.id ");
    sql.append(" where b.deleted=0 and p.deleted=0 ");
    sql.append(" and ((b.state ='发起费用清单' and p.state='发起费用清单' and DATE_SUB(CURDATE(), INTERVAL 3 DAY) < date(p.create_time)) ");
    sql.append(" or (b.state ='应付费用已审核' and p.state='应付费用已审核' and DATE_SUB(CURDATE(), INTERVAL 2 DAY) < date(p.create_time))) ");
    getMapParameter(sql, map);
    return jdbcTemplate.query(sql.toString(), map, rm);
  }

  @Override
  public Bill getById(Bill bill) {
    RowMapper<Bill> rm = new BeanPropertyRowMapper<Bill>(Bill.class);
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("select b.id,b.organization_id,b.customer_id,b.customer");
    sql.append(",b.contacts");
    sql.append(",CASE b.contacts_phone WHEN '' THEN cm.admin_user ");
    sql.append(" WHEN null THEN cm.admin_user ELSE b.contacts_phone END as contacts_phone ");
    sql.append(",b.dock,b.province");
    sql.append(",b.address,b.weight,b.box_type,b.price");
    sql.append(",b.pack_date,b.vessel,b.voyage,b.bill_of_lading");
    sql.append(",b.barcode_effective_date,b.earliest_arrival_port_date");
    sql.append(",b.factory_address,b.factory_phone,b.qr_code,b.car_team_id,b.car_team");
    sql.append(",b.ef_yard,b.ef_dock");
    sql.append(",b.driver,b.driver_id,b.driver_phone,b.car_no,b.driver_freight,b.box_num");
    sql.append(",b.sealno,b.sealno_pic,b.box_num_pic,b.arrived_factory_latitude,b.arrived_factory_longitude");
    sql.append(",b.arrived_factory_date,b.arrived_factory_warn_date,b.leaved_factory_date,b.leaved_factory_longitude");
    sql.append(",b.leaved_factory_latitude,b.arrived_harbour_date,b.arrived_harbour_latitude,b.arrived_harbour_longitude");
    sql.append(",b.arrived_harbour_warn_date,b.annex,b.state,b.fee_state_s,b.fee_state_f,b.pre_entry_pic");
    sql.append(",b.up_yard,b.back_yard,b.remark,b.feeRemark,b.plat_remark,b.finance_remark,b.create_time");
    sql.append(",b.offertime,b.maketime");
    sql.append(",b.salesmanId,b.salesman,b.operaterId,b.operater");
    sql.append(",b.arrival_port,b.sfile,b.white_card,b.sealed_land");
    sql.append(",b.driverReceivetime,b.upBoxtime,b.arrivedFactorytime,b.leaveFactorytime");
    sql.append(",b.inPorttime,b.costBilltime,b.verifytime,b.driverFeeVerifytime,b.customerVerifytime");
    sql.append(",b.create_user,b.update_time,b.update_user");
    sql.append(",cm.email,b.updated,b.drop_box,b.withholding,b.cash");
    sql.append(",sognz.organization_name organizationName,suser.nick_name createName ");
    sql.append(" from chf_bill b ");
    sql.append(" left join chf_customer cm on cm.id=b.customer_id ");
    sql.append(" inner join sys_user suser on suser.user_id=b.create_user ");
    sql.append(" inner join sys_organization sognz on sognz.organization_id=b.organization_id ");
    sql.append(" where b.deleted=0 and cm.deleted=0 and sognz.deleted=0 ");
    sql.append(" and b.id=:id ");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    return jdbcTemplate.queryForObject(sql.toString(), map, rm);
  }

  @Override
  public boolean save(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("insert into chf_bill (");
    sql.append("organization_id,customer_id,customer,contacts,contacts_phone");
    sql.append(",salesmanId,salesman,operaterId,operater");
    sql.append(",dock,province,address,weight");
    sql.append(",box_type,price,pack_date,vessel");
    sql.append(",voyage,bill_of_lading,factory_address,factory_phone");
    sql.append(",barcode_effective_date,earliest_arrival_port_date");
    sql.append(",qr_code,car_team_id,car_team,driver,driver_id,driver_phone");
    sql.append(",ef_yard,ef_dock");
    sql.append(",car_no,driver_freight,box_num,sealno,sealno_pic,box_num_pic");
    sql.append(",arrived_factory_latitude,arrived_factory_longitude,arrived_factory_date,arrived_factory_warn_date");
    sql.append(",leaved_factory_date,leaved_factory_longitude,leaved_factory_latitude,arrived_harbour_date");
    sql.append(",arrived_harbour_latitude,arrived_harbour_longitude,arrived_harbour_warn_date,annex,state,fee_state_s,fee_state_f,pre_entry_pic");
    sql.append(",up_yard,back_yard");
    sql.append(",arrival_port,sfile,white_card,sealed_land");
    sql.append(",remark,plat_remark,finance_remark,create_user) values ( ");
    sql.append(":organization_id,:customer_id,:customer,:contacts,:contacts_phone");
    sql.append(",:salesmanId,:salesman,:operaterId,:operater");
    sql.append(",:dock,:province,:address,:weight");
    sql.append(",:box_type,:price,:pack_date,:vessel");
    sql.append(",:voyage,:bill_of_lading,:factory_address,:factory_phone");
    sql.append(",:barcode_effective_date,:earliest_arrival_port_date");
    sql.append(",:qr_code,:car_team_id,:car_team,:driver,:driver_id,:driver_phone");
    sql.append(",:ef_yard,:ef_dock");
    sql.append(",:car_no,:driver_freight,:box_num,:sealno,:sealno_pic,:box_num_pic");
    sql.append(",:arrived_factory_latitude,:arrived_factory_longitude,:arrived_factory_date,:arrived_factory_warn_date");
    sql.append(",:leaved_factory_date,:leaved_factory_longitude,:leaved_factory_latitude,:arrived_harbour_date");
    sql.append(",:arrived_harbour_latitude,:arrived_harbour_longitude,:arrived_harbour_warn_date,:annex,:state,:fee_state_s,:fee_state_f,:pre_entry_pic");
    sql.append(",:up_yard,:back_yard");
    sql.append(",:arrival_port,:sfile,:white_card,:sealed_land");
    sql.append(",:remark,:plat_remark,:finance_remark,:create_user)");
    map.put("organization_id", loginUser.getOrganizationId() + "");
    map.put("customer", bill.getCustomer());
    map.put("customer_id", bill.getCustomerId());
    map.put("contacts", bill.getContacts());
    map.put("contacts_phone", bill.getContactsPhone());
    map.put("salesmanId", bill.getSalesmanId());
    map.put("salesman", bill.getSalesman());
    map.put("operaterId", bill.getOperaterId());
    map.put("operater", bill.getOperater());
    map.put("dock", bill.getDock());
    map.put("province", bill.getProvince());
    map.put("address", bill.getAddress());
    map.put("weight", bill.getWeight());
    map.put("box_type", bill.getBoxType());
    map.put("price", bill.getPrice());
    map.put("pack_date", bill.getPackDate());
    map.put("vessel", bill.getVessel());
    map.put("voyage", bill.getVoyage());
    map.put("bill_of_lading", bill.getBillOfLading());
    map.put("barcode_effective_date", bill.getBarcodeEffectiveDate());
    map.put("earliest_arrival_port_date", bill.getEarliestArrivalPortDate());
    map.put("factory_address", bill.getFactoryAddress());
    map.put("factory_phone", bill.getFactoryPhone());
    map.put("qr_code", bill.getQrCode());
    map.put("car_team_id", bill.getCarTeamId());
    map.put("car_team", bill.getCarTeam());
    map.put("ef_yard", bill.getEfYard());
    map.put("ef_dock", bill.getEfDock());
    map.put("driver", bill.getDriver());
    map.put("driver_id", bill.getDriverId());
    map.put("driver_phone", bill.getDriverPhone());
    map.put("car_no", bill.getCarNo());
    map.put("driver_freight", bill.getDriverFreight());
    map.put("box_num", bill.getBoxNum());
    map.put("box_num_pic", bill.getBoxNumPic());
    map.put("sealno", bill.getSealno());
    map.put("sealno_pic", bill.getSealnoPic());
    map.put("arrived_factory_latitude", bill.getArrivedFactoryLatitude());
    map.put("arrived_factory_longitude", bill.getArrivedFactoryLongitude());
    map.put("arrived_factory_date", bill.getArrivedFactoryDate());
    map.put("arrived_factory_warn_date", bill.getArrivedFactoryWarnDate());
    map.put("leaved_factory_date", bill.getLeavedFactoryDate());
    map.put("leaved_factory_longitude", bill.getLeavedFactoryLongitude());
    map.put("leaved_factory_latitude", bill.getLeavedFactoryLatitude());
    map.put("arrived_harbour_date", bill.getArrivedHarbourDate());
    map.put("arrived_harbour_latitude", bill.getArrivedHarbourLatitude());
    map.put("arrived_harbour_longitude", bill.getArrivedHarbourLongitude());
    map.put("arrived_harbour_warn_date", bill.getArrivedHarbourWarnDate());
    map.put("pre_entry_pic", bill.getPreEntryPic());
    map.put("annex", bill.getAnnex());
    map.put("state", bill.getState());
    map.put("fee_state_s", bill.getFeeStateS());
    map.put("fee_state_f", bill.getFeeStateF());
    map.put("up_yard", bill.getUpYard());
    map.put("back_yard", bill.getBackYard());
    map.put("arrival_port", bill.getArrivalPort());
    map.put("sfile", bill.getSfile());
    map.put("white_card", bill.getWhiteCard());
    map.put("sealed_land", bill.getSealedLand());
    map.put("remark", bill.getRemark());
    map.put("plat_remark", bill.getPlatRemark());
    map.put("finance_remark", bill.getFinanceRemark());
    map.put("create_user", loginUser.getUserId() + "");
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public Integer order(Bill bill, User loginUser) {
    GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
    KeyHolder keyHolder = new GeneratedKeyHolder();
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("insert into chf_bill (");
    sql.append("organization_id,customer_id,customer,contacts,contacts_phone");
    sql.append(",salesmanId,salesman,operaterId,operater");
    sql.append(",dock,province,address,weight");
    sql.append(",box_type,price,driver_freight,sys_driver_freight,pack_date,vessel");
    sql.append(",voyage,bill_of_lading,factory_address,factory_phone");
    sql.append(",barcode_effective_date,earliest_arrival_port_date");
    sql.append(",ef_yard,ef_dock");
    sql.append(",arrived_factory_warn_date,arrived_harbour_warn_date");
    sql.append(",qr_code,annex,state,fee_state_s,fee_state_f,offertime");
    sql.append(",arrival_port,sfile,white_card,sealed_land");
    sql.append(",remark,create_user)");
    sql.append(" values (");
    sql.append(":organization_id,:customer_id,:customer,:contacts,:contacts_phone");
    sql.append(",:salesmanId,:salesman,:operaterId,:operater");
    sql.append(",:dock,:province,:address,:weight");
    sql.append(",:box_type,:price,:driver_freight,:sys_driver_freight,:pack_date,:vessel");
    sql.append(",:voyage,:bill_of_lading,:factory_address,:factory_phone");
    sql.append(",:barcode_effective_date,:earliest_arrival_port_date");
    sql.append(",:ef_yard,:ef_dock");
    sql.append(",:arrived_factory_warn_date,:arrived_harbour_warn_date");
    sql.append(",:qr_code,:annex,:state,:fee_state_s,:fee_state_f,now()");
    sql.append(",:arrival_port,:sfile,:white_card,:sealed_land");
    sql.append(",:remark,:create_user)");
    map.put("organization_id", loginUser.getOrganizationId() + "");
    map.put("customer", bill.getCustomer());
    map.put("customer_id", bill.getCustomerId());
    map.put("contacts", bill.getContacts());
    map.put("contacts_phone", bill.getContactsPhone());
    map.put("salesmanId", bill.getSalesmanId());
    map.put("salesman", bill.getSalesman());
    map.put("operaterId", bill.getOperaterId());
    map.put("operater", bill.getOperater());
    map.put("dock", bill.getDock());
    map.put("province", bill.getProvince());
    map.put("address", bill.getAddress());
    map.put("weight", bill.getWeight());
    map.put("box_type", bill.getBoxType());
    map.put("price", bill.getPrice());
    map.put("driver_freight", bill.getDriverFreight());
    map.put("sys_driver_freight", bill.getSysDriverFreight());
    map.put("pack_date", bill.getPackDate());
    map.put("vessel", bill.getVessel());
    map.put("voyage", bill.getVoyage());
    map.put("bill_of_lading", bill.getBillOfLading());
    map.put("factory_address", bill.getFactoryAddress());
    map.put("factory_phone", bill.getFactoryPhone());
    map.put("barcode_effective_date", bill.getBarcodeEffectiveDate());
    map.put("earliest_arrival_port_date", bill.getEarliestArrivalPortDate());
    map.put("ef_yard", bill.getEfYard());
    map.put("ef_dock", bill.getEfDock());
    map.put("qr_code", bill.getQrCode());
    map.put("arrived_factory_warn_date", bill.getArrivedFactoryWarnDate());
    map.put("arrived_harbour_warn_date", bill.getArrivedHarbourWarnDate());
    map.put("annex", bill.getAnnex());
    map.put("state", bill.getState());
    map.put("fee_state_s", bill.getFeeStateS());
    map.put("fee_state_f", bill.getFeeStateF());
    map.put("arrival_port", bill.getArrivalPort());
    map.put("sfile", bill.getSfile());
    map.put("white_card", bill.getWhiteCard());
    map.put("sealed_land", bill.getSealedLand());
    map.put("remark", bill.getRemark());
    map.put("create_user", loginUser.getUserId() + "");
    getMapParameter(sql, map);
    //		System.err.println("受到影响的行数=" + retVal + ",
    int retVal = this.jdbcTemplate.update(sql.toString(), new SqlParameterSource() {
      @Override
      public boolean hasValue(String paramName) {
        return map.containsKey(paramName);
      }

      @Override
      public Object getValue(String paramName) throws IllegalArgumentException {
        return map.get(paramName);
      }
    }, generatedKeyHolder);

    Integer id = generatedKeyHolder.getKey().intValue();
    return id;
  }

  @Override
  public boolean platReceive_update(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill set ");
    sql.append("dock=:dock,province=:province,address=:address,weight=:weight");
    sql.append(",box_type=:box_type,price=:price,driver_freight=:driver_freight");
    sql.append(",sys_driver_freight=:sys_driver_freight,pack_date=:pack_date,vessel=:vessel");
    sql.append(",voyage=:voyage,bill_of_lading=:bill_of_lading,sealed_land=:sealed_land");
    sql.append(",factory_address=:factory_address,factory_phone=:factory_phone");
    sql.append(",barcode_effective_date=:barcode_effective_date,earliest_arrival_port_date=:earliest_arrival_port_date");
    sql.append(",ef_yard=:ef_yard,ef_dock=:ef_dock");
    sql.append(",annex=:annex,state=:state");
    sql.append(",update_user=:update_user where id=:id ");
    map.put("dock", bill.getDock());
    map.put("province", bill.getProvince());
    map.put("address", bill.getAddress());
    map.put("weight", bill.getWeight());
    map.put("box_type", bill.getBoxType());
    map.put("price", bill.getPrice());
    map.put("driver_freight", bill.getDriverFreight());
    map.put("sys_driver_freight", bill.getSysDriverFreight());
    map.put("pack_date", bill.getPackDate());
    map.put("vessel", bill.getVessel());
    map.put("voyage", bill.getVoyage());
    map.put("bill_of_lading", bill.getBillOfLading());
    map.put("sealed_land", bill.getSealedLand());
    map.put("factory_address", bill.getFactoryAddress());
    map.put("factory_phone", bill.getFactoryPhone());
    map.put("barcode_effective_date", bill.getBarcodeEffectiveDate());
    map.put("earliest_arrival_port_date", bill.getEarliestArrivalPortDate());
    map.put("ef_yard", bill.getEfYard());
    map.put("ef_dock", bill.getEfDock());
    map.put("annex", bill.getAnnex());
    map.put("state", bill.getState());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean platReceive(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill set ");
    sql.append(" state=:state");
    sql.append(",update_user=:update_user where id=:id ");
    map.put("state", bill.getState());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean carTeam(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill set ");
    sql.append(" car_team_id=:car_team_id");
    sql.append(" ,car_team=:car_team,state=:state ");
//		sql.append(" ,ef_yard=:ef_yard");
    sql.append(",car_no=''");
    sql.append(",driver_phone='',driver='',driver_id=0 ");
    sql.append(",white_card=''");
    sql.append(",update_user=:update_user where id=:id ");
    map.put("car_team_id", bill.getCarTeamId());
    map.put("car_team", bill.getCarTeam());
    map.put("state", bill.getState());
//		map.put("ef_yard", bill.getEfYard());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean make(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill set ");
    sql.append("driver=:driver,driver_id=:driver_id,price=:price ");
    sql.append(",driver_freight=:driver_freight,sys_driver_freight=:sys_driver_freight");
//		sql.append(" ,ef_yard=:ef_yard");
    sql.append(",white_card=:white_card");
    sql.append(",sfile=:sfile");
    if (Utils.isNotEmpty(bill.getCarTeamId())) {
      sql.append(",car_team_id=:car_team_id,car_team=:car_team");
    }
    sql.append(",driver_phone=:driver_phone,car_no=:car_no" +
            ",state=:state,maketime=now() ");
    sql.append(",update_user=:update_user where id=:id ");
    map.put("price", bill.getPrice());
    map.put("driver", bill.getDriver());
    map.put("driver_id", bill.getDriverId());
    map.put("driver_freight", bill.getDriverFreight());
    map.put("sys_driver_freight", bill.getSysDriverFreight());
//		map.put("ef_yard", bill.getEfYard());
    map.put("white_card", bill.getWhiteCard());
    map.put("sfile", bill.getSfile());
    if (Utils.isNotEmpty(bill.getCarTeamId())) {
      map.put("car_team_id", bill.getCarTeamId());
      map.put("car_team", bill.getCarTeam());
    }
    map.put("driver_phone", bill.getDriverPhone());
    map.put("car_no", bill.getCarNo());
    map.put("state", bill.getState());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }


  @Override
  public boolean driverReceive(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill set ");
    sql.append(" state=:state,driverReceivetime=now() ");
    sql.append(",update_user=:update_user where id=:id ");
    map.put("state", bill.getState());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean upBox_substitute(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill set ");
    sql.append("box_num=:box_num,box_num_pic=:box_num_pic ");
    sql.append(",sealno=:sealno,sealno_pic=:sealno_pic ");
//		sql.append(",arrival_port=:arrival_port,up_yard=:up_yard ");
    sql.append(",update_user=:update_user where id=:id ");
    map.put("box_num", bill.getBoxNum());
    map.put("box_num_pic", bill.getBoxNumPic());
    map.put("sealno", bill.getSealno());
    map.put("sealno_pic", bill.getSealnoPic());
//		map.put("arrival_port", bill.getArrivalPort());
//		map.put("up_yard", bill.getUpYard());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean upBox_gps(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill set ");
    sql.append("state=:state,upBoxtime=now() ");
    sql.append(",update_user=:update_user where id=:id ");
    map.put("state", bill.getState());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean upBox(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill set ");
    sql.append("box_num=:box_num,arrival_port=:arrival_port,up_yard=:up_yard ");
    sql.append(",sealno=:sealno,sealno_pic=:sealno_pic,box_num_pic=:box_num_pic" +
            ",state=:state,upBoxtime=now() ");
    sql.append(",update_user=:update_user where id=:id ");
    map.put("box_num", bill.getBoxNum());
    map.put("arrival_port", bill.getArrivalPort());
    map.put("up_yard", bill.getUpYard());
    map.put("box_num_pic", bill.getBoxNumPic());
    map.put("sealno", bill.getSealno());
    map.put("sealno_pic", bill.getSealnoPic());
    map.put("state", bill.getState());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean arrivedFactory(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill set ");
    sql.append("arrived_factory_latitude=:arrived_factory_latitude ");
    sql.append(",arrived_factory_longitude=:arrived_factory_longitude," +
            "arrived_factory_date=:arrived_factory_date ");
    sql.append(",state=:state,arrivedFactorytime=now() ");
    sql.append(",update_user=:update_user where id=:id ");
    map.put("arrived_factory_latitude", bill.getArrivedFactoryLatitude());
    map.put("arrived_factory_longitude", bill.getArrivedFactoryLongitude());
    map.put("arrived_factory_date", bill.getArrivedFactoryDate());
    map.put("state", bill.getState());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean leaveFactory(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill set ");
    sql.append("leaved_factory_date=:leaved_factory_date,leaved_factory_longitude=:leaved_factory_longitude," +
            "leaved_factory_latitude=:leaved_factory_latitude ");
    sql.append(",state=:state,leaveFactorytime=now() ");
    sql.append(",update_user=:update_user where id=:id ");
    map.put("leaved_factory_date", bill.getLeavedFactoryDate());
    map.put("leaved_factory_longitude", bill.getLeavedFactoryLongitude());
    map.put("leaved_factory_latitude", bill.getLeavedFactoryLatitude());
    map.put("state", bill.getState());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean inPort_substitute(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill set ");
    sql.append("pre_entry_pic=:pre_entry_pic ");
    sql.append(",update_user=:update_user where id=:id ");
    map.put("pre_entry_pic", bill.getPreEntryPic());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean inPort_gps(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill set ");
    sql.append("state=:state ");
    sql.append(",update_user=:update_user where id=:id ");
    map.put("state", bill.getState());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean inPort(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill set ");
    sql.append("arrived_harbour_date=:arrived_harbour_date,arrived_harbour_latitude=:arrived_harbour_latitude," +
            "arrived_harbour_longitude=:arrived_harbour_longitude,pre_entry_pic=:pre_entry_pic ");
    sql.append(",state=:state,inPorttime=now() ");
    sql.append(",update_user=:update_user where id=:id ");
    map.put("arrived_harbour_date", bill.getArrivedHarbourDate());
    map.put("arrived_harbour_latitude", bill.getArrivedHarbourLatitude());
    map.put("arrived_harbour_longitude", bill.getArrivedHarbourLongitude());
    map.put("pre_entry_pic", bill.getPreEntryPic());
    map.put("state", bill.getState());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean costBill(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill set ");
    sql.append("state=:state,costBilltime=now() ");
    sql.append(",update_user=:update_user where id=:id ");
    map.put("state", bill.getState());
//		map.put("plat_remark", bill.getPlatRemark());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean updateState(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill set ");
    sql.append("state=:state");
    sql.append(",update_user=:update_user where id=:id ");
    map.put("state", bill.getState());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

	/*@Override
	public boolean verify(Bill bill, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update chf_bill set ");
		sql.append("state=:state,verifytime=now() ");
		sql.append(",update_user=:update_user where id=:id ");
		map.put("state", bill.getState());
		map.put("update_user", loginUser.getUserId() + "");
		map.put("id", bill.getId());
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public boolean driverFeeVerify(Bill bill, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update chf_bill set ");
		sql.append("state=:state,driverFeeVerifytime=now() ");
		sql.append(",update_user=:update_user where id=:id ");
		map.put("state", bill.getState());
		map.put("update_user", loginUser.getUserId() + "");
		map.put("id", bill.getId());
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public boolean customerVerify(Bill bill, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update chf_bill set ");
		sql.append("state=:state,customerVerifytime=now() ");
		sql.append(",update_user=:update_user where id=:id ");
		map.put("state", bill.getState());
		map.put("update_user", loginUser.getUserId() + "");
		map.put("id", bill.getId());
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public boolean customerDisagree(Bill bill, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update chf_bill set ");
		sql.append("state=:state, feeRemark=:feeRemark ");
		sql.append(",update_user=:update_user where id=:id ");
		map.put("state", bill.getState());
		map.put("feeRemark", bill.getFeeRemark());
		map.put("update_user", loginUser.getUserId() + "");
		map.put("id", bill.getId());
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}*/

  @Override
  public boolean updateFinance(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill set ");
    sql.append("finance_remark=:finance_remark ");
    sql.append(",update_user=:update_user where id=:id ");
    map.put("finance_remark", bill.getFinanceRemark());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean updateRemark(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill set ");
    sql.append("plat_remark=:plat_remark,remark=:remark ");
    sql.append(",update_user=:update_user where id=:id ");
    map.put("plat_remark", bill.getPlatRemark());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean updateTotalFee_S_F(Bill bill, User loginUser) {
		/*Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update chf_bill set ");
		sql.append("fee_total_f=:fee_total_f ");
		sql.append(",fee_total_s=:fee_total_s ");
		sql.append(",update_user=:update_user where id=:id ");
		map.put("fee_total_f", bill.getFeeTotalF());
		map.put("fee_total_s", bill.getFeeTotalS());
		map.put("update_user", loginUser.getUserId() + "");
		map.put("id", bill.getId());
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;*/
    return true;
  }

  @Override
  public boolean update(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill set ");
    sql.append("customer_id=:customer_id,customer=:customer,contacts=:contacts,contacts_phone=:contacts_phone");
    sql.append(",dock=:dock,province=:province,address=:address,weight=:weight");
    sql.append(",box_type=:box_type,price=:price,pack_date=:pack_date,vessel=:vessel");
    sql.append(",voyage=:voyage,bill_of_lading=:bill_of_lading,factory_address=:factory_address" +
            ",factory_phone=:factory_phone");
    sql.append(",qr_code=:qr_code,driver=:driver,driver_id=:driver_id,sealed_land=:sealed_land");
    sql.append(",barcode_effective_date=:barcode_effective_date,earliest_arrival_port_date=:earliest_arrival_port_date");
    sql.append(",ef_yard=:ef_yard,ef_dock=:ef_dock");
    sql.append(",driver_phone=:driver_phone,car_no=:car_no,driver_freight=:driver_freight,box_num=:box_num");
    sql.append(",box_num_pic=:box_num_pic,sealno=:sealno,sealno_pic=:sealno_pic");
    sql.append(",arrived_factory_latitude=:arrived_factory_latitude" +
            ",arrived_factory_longitude=:arrived_factory_longitude" +
            ",arrived_factory_date=:arrived_factory_date");
    sql.append(",arrived_factory_warn_date=:arrived_factory_warn_date");
    sql.append(",leaved_factory_date=:leaved_factory_date" +
            ",leaved_factory_longitude=:leaved_factory_longitude" +
            ",leaved_factory_latitude=:leaved_factory_latitude");
    sql.append(",arrived_harbour_date=:arrived_harbour_date" +
            ",arrived_harbour_latitude=:arrived_harbour_latitude" +
            ",arrived_harbour_longitude=:arrived_harbour_longitude");
    sql.append(",arrived_harbour_warn_date=:arrived_harbour_warn_date");
    sql.append(",pre_entry_pic=:pre_entry_pic");
    sql.append(",up_yard=:up_yard,back_yard=:back_yard");
    sql.append(",arrival_port=:arrival_port,sfile=:sfile,white_card=:white_card");
    sql.append(",remark=:remark,plat_remark=:plat_remark");
    sql.append(",annex=:annex,updated=:updated");
//		sql.append(",salesmanId=:salesmanId,salesman=:salesman");
//		sql.append(",operaterId=:operaterId,operater=:operater");
    sql.append(",update_user=:update_user where id=:id ");
    map.put("customer_id", bill.getCustomerId());
    map.put("customer", bill.getCustomer());
    map.put("contacts", bill.getContacts());
    map.put("contacts_phone", bill.getContactsPhone());
    map.put("dock", bill.getDock());
    map.put("province", bill.getProvince());
    map.put("address", bill.getAddress());
    map.put("weight", bill.getWeight());
    map.put("box_type", bill.getBoxType());
    map.put("price", bill.getPrice());
    map.put("pack_date", bill.getPackDate());
    map.put("vessel", bill.getVessel());
    map.put("voyage", bill.getVoyage());
    map.put("bill_of_lading", bill.getBillOfLading());
    map.put("factory_address", bill.getFactoryAddress());
    map.put("factory_phone", bill.getFactoryPhone());
    map.put("qr_code", bill.getQrCode());
//        map.put("car_team", bill.getCarTeam());
    map.put("driver_id", bill.getDriverId());
    map.put("driver", bill.getDriver());
    map.put("sealed_land", bill.getSealedLand());
    map.put("barcode_effective_date", bill.getBarcodeEffectiveDate());
    map.put("earliest_arrival_port_date", bill.getEarliestArrivalPortDate());
    map.put("ef_yard", bill.getEfYard());
    map.put("ef_dock", bill.getEfDock());
    map.put("arrival_port", bill.getArrivalPort());
    map.put("sfile", bill.getSfile());
    map.put("white_card", bill.getWhiteCard());
    map.put("driver_phone", bill.getDriverPhone());
    map.put("car_no", bill.getCarNo());
    map.put("driver_freight", bill.getDriverFreight());
    map.put("box_num", bill.getBoxNum());
    map.put("box_num_pic", bill.getBoxNumPic());
    map.put("sealno", bill.getSealno());
    map.put("sealno_pic", bill.getSealnoPic());
    map.put("arrived_factory_latitude", bill.getArrivedFactoryLatitude());
    map.put("arrived_factory_longitude", bill.getArrivedFactoryLongitude());
    map.put("arrived_factory_date", bill.getArrivedFactoryDate());
    map.put("arrived_factory_warn_date", bill.getArrivedFactoryWarnDate());
    map.put("leaved_factory_date", bill.getLeavedFactoryDate());
    map.put("leaved_factory_longitude", bill.getLeavedFactoryLongitude());
    map.put("leaved_factory_latitude", bill.getLeavedFactoryLatitude());
    map.put("arrived_harbour_date", bill.getArrivedHarbourDate());
    map.put("arrived_harbour_latitude", bill.getArrivedHarbourLatitude());
    map.put("arrived_harbour_longitude", bill.getArrivedHarbourLongitude());
    map.put("arrived_harbour_warn_date", bill.getArrivedHarbourWarnDate());
    map.put("pre_entry_pic", bill.getPreEntryPic());
    map.put("remark", bill.getRemark());
    map.put("plat_remark", bill.getPlatRemark());
    map.put("annex", bill.getAnnex());
    map.put("updated", bill.getUpdated());
    map.put("up_yard", bill.getUpYard());
    map.put("back_yard", bill.getBackYard());
//		map.put("salesmanId", bill.getSalesmanId());
//		map.put("salesman", bill.getSalesman());
//		map.put("operaterId", bill.getOperaterId());
//		map.put("operater", bill.getOperater());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean updateBoxNumPic(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill set ");
    sql.append("box_num_pic=:box_num_pic");
    sql.append(",update_user=:update_user,update_time=now() where id=:id ");
    map.put("box_num_pic", bill.getBoxNumPic());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean updateSealnoPic(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill set ");
    sql.append("sealno_pic=:sealno_pic");
    sql.append(",update_user=:update_user,update_time=now() where id=:id ");
    map.put("sealno_pic", bill.getSealnoPic());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean updatePreEntryPic(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill set ");
    sql.append("pre_entry_pic=:pre_entry_pic");
    sql.append(",update_user=:update_user,update_time=now() where id=:id ");
    map.put("pre_entry_pic", bill.getPreEntryPic());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean updateAnnex(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill set ");
    sql.append("annex=:annex");
    sql.append(",update_user=:update_user,update_time=now() where id=:id ");
    map.put("annex", bill.getAnnex());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean updateSfile(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill set ");
    sql.append("sfile=:sfile");
    sql.append(",update_user=:update_user,update_time=now() where id=:id ");
    map.put("sfile", bill.getSfile());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean feeStateS(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill set ");
    sql.append("fee_state_s=:fee_state_s");
    sql.append(",update_user=:update_user,update_time=now() where id=:id ");
    map.put("fee_state_s", bill.getFeeStateS());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean feeStateF(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("update chf_bill set ");
    sql.append("fee_state_f=:fee_state_f");
    sql.append(",update_user=:update_user,update_time=now() where id=:id ");
    map.put("fee_state_f", bill.getFeeStateF());
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean changeDropBox(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("");
    sql.append("update chf_bill set drop_box=if(drop_box=1,0,1) ");
    sql.append(",update_user=:update_user,update_time=now() where id=:id ");
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean changeWithholdingBox(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("");
    sql.append("update chf_bill set withholding=if(withholding=1,0,1) ");
    sql.append(",update_user=:update_user,update_time=now() where id=:id ");
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean changeCash(Bill bill, User loginUser) {
    Map<String, String> map = new HashMap<String, String>();
    StringBuffer sql = new StringBuffer("");
    sql.append("update chf_bill set cash=if(cash=1,0,1) ");
    sql.append(",update_user=:update_user,update_time=now() where id=:id ");
    map.put("update_user", loginUser.getUserId() + "");
    map.put("id", bill.getId());
    getMapParameter(sql, map);
    int count = jdbcTemplate.update(sql.toString(), map);
    return count > 0;
  }

  @Override
  public boolean remove(Bill bill, User loginUser) {
    int count = 0;
    Map<String, String> map = null;
    if (bill.getId().length() > 0) {
      for (String ID : bill.getId().split(",")) {
        map = new HashMap<String, String>();
        if (ID == null || ID.equals("")) {
          break;
        }
        StringBuffer sql = new StringBuffer("update chf_bill set deleted=1" +
                ",update_user=:update_user, update_time=now() "
                + " where id =:id ");
        map.put("update_user", loginUser.getUserId() + "");
        map.put("id", ID);
        getMapParameter(sql, map);
        count = jdbcTemplate.update(sql.toString(), map);
      }
    }
    return count > 0;
  }

  @Override
  public boolean billRecover(Bill bill, User loginUser) {
    int count = 0;
    Map<String, String> map = null;
    if (bill.getId().length() > 0) {
      for (String ID : bill.getId().split(",")) {
        map = new HashMap<String, String>();
        if (ID == null || ID.equals("")) {
          break;
        }
        StringBuffer sql = new StringBuffer("update chf_bill set deleted=0" +
                ",update_user=:update_user, update_time=now() "
                + " where id =:id ");
        map.put("update_user", loginUser.getUserId() + "");
        map.put("id", ID);
        getMapParameter(sql, map);
        count = jdbcTemplate.update(sql.toString(), map);
      }
    }
    return count > 0;
  }


  @Override
  public JsonResult writeOffAccountsList(PageResult2<T> page, WriteOff bill, User loginUser) throws Exception {
    Map<String, String> map = new HashMap<String, String>();
    RowMapper<WriteOff> rm = new BeanPropertyRowMapper<WriteOff>(WriteOff.class);
    StringBuffer sql = new StringBuffer("");
    sql.append(" select distinct b.id, b.bill_of_lading, b.box_num,b.pack_date, bf.invoicename,bf.type,bf.price,bf.settlement,bf.settlement_price ");
//		sql.append(" b.write_off_accounts_price_ys,b.write_off_accounts_price_yf, ");
//		sql.append(" b.write_off_accounts_type_ys,b.write_off_accounts_type_yf ");
//		sql.append(" if(bf.type='应收',b.write_off_accounts_price_ys,b.write_off_accounts_price_yf) write_off_accounts_price, ");
//		sql.append(" if(bf.type='应收',b.write_off_accounts_type_ys,b.write_off_accounts_type_yf) write_off_accounts_type ");
    sql.append("from chf_bill b ");
    sql.append("inner join chf_bill_process blp on blp.bill_id=b.id ");
    sql.append("inner join (select bf2.bill_id, bf2.invoicename,bf2.type,max(settlement) settlement,max(settlement_price) settlement_price,sum(bf2.price) price ");
//		sql.append("sum(if(bf2.invoicestate=1,round(bf2.price/"+UserConstant.INVOICENUM+",2),bf2.price)) price ");
    sql.append("from chf_bill_fee bf2 ");
    sql.append("where bf2.deleted=0 and bf2.invoicename !='' ");
    sql.append("group by bf2.bill_id, bf2.type,bf2.invoicename) bf on b.id=bf.bill_id ");
    sql.append(" where b.deleted=0 ");
    sql.append(" and blp.state='财务应收应付费用已审核' ");
    if (Utils.isNotEmpty(bill)) {
      if (Utils.isNotEmpty(bill.getInvoicename())) {
        sql.append(" and bf.invoicename=:invoicename ");
        map.put("invoicename", bill.getInvoicename());
      }
      if (Utils.isNotEmpty(bill.getType())) {
        sql.append(" and bf.type=:type ");
        map.put("type", bill.getType());
      }
      if (Utils.isNotEmpty(bill.getWriteOff()) && bill.getWriteOff().equals("writeOff")) {
//				sql.append(" and if(bf.type='应收',b.write_off_accounts_type_ys!='全部',b.write_off_accounts_type_yf!='全部')  ");
        sql.append(" and bf.settlement !='2'  ");
      } else if (Utils.isNotEmpty(bill.getWriteOff()) && bill.getWriteOff().equals("unWriteOff")) {
//				sql.append(" and if(bf.type='应收',b.write_off_accounts_type_ys!='全部',b.write_off_accounts_type_yf!='全部')  ");
        sql.append(" and bf.settlement ='2'  ");
      }
      if (Utils.isNotEmpty(bill.getBillOfLading())) {
        sql.append(" and instr(b.bill_of_lading,:bill_of_lading)>0 ");
        map.put("bill_of_lading", bill.getBillOfLading());
      }
      if (Utils.isNotEmpty(bill.getBoxNum())) {
        sql.append(" and instr(b.box_num,:box_num)>0 ");
        map.put("box_num", bill.getBoxNum());
      }
      if (Utils.isNotEmpty(bill.getDate1())) {
        sql.append(" and b.pack_date>= :date1 ");
        map.put("date1", bill.getDate1());
      }
      if (Utils.isNotEmpty(bill.getDate2())) {
        sql.append(" and b.pack_date<=:date2 ");
        map.put("date2", bill.getDate2());
      }
      if (Utils.isNotEmpty(bill.getSort()) && Utils.isNotEmpty(bill.getOrder())) {
        sql.append(" order by " + bill.getSort() + " " + bill.getOrder());
      } else if (Utils.isNotEmpty(bill.getSort()) && bill.getSort().equals("customer_id")) {
        sql.append(" order by b.customer_id asc, b.pack_date desc");
      } else {
        sql.append(" order by b.pack_date desc");
      }
    }
    return jsonResult(getPageQueryList(page, sql.toString(), map, rm));
  }

}
